MACROS - Using macros language, we can customize and reduce SAS language. - Using macros language, we can develop reusable application. - Macros language is character based language. - If we want to develop macro application in SAS, we need 2 requirements
This is one of the part in SAS.
It can be used interact with macroprocessor.
It can be used to identify macrolanguage.
This is called macro reference Each and every macro statement starts with %.
This is called macrovariable reference. It can be used for reporting macrovariable. - Macro coding can be written outside and inside of the macroblock. - Macro block starts with % macro and requires name of the macro and closed with % mend.
% Macro <Macroname>; SAS coding (include dataset block, proc block, open code) % Mend; we run the macro application, SAS do compilation and stores compilation coding in catalog. Catalog name same name of macro.
Interested in mastering SAS Certification? Enroll now for FREE demo on SAS Training.
Macro Call: To call required macro for execution Syntax: % <Macroname>; Eg: %macro print; proc print; run; % mend; data demo; input pid age; cards; 100 89 101 90 ; % print; data medi; input pd drug &; cards; 100 5mg 101 6mg ; % print; - Catalog defaults stores in work library.
Macrovariables are 3 types
- All macrovariables work like character variables. a) Global macrovariable Creation: Note: In one macrovariable we can store upto 32,767 characters. - Global macrovariable creation can be done anywhere in programming coding (inside/outside of he macroblock) and we can use anywhere in programming coding. % global Statement: It can be used to create global macrovariable Syntax: %global <macrovariable>; % let Statement: It can be used to assign required value to macrovariable. Syntax: % let <macrovariable(name)> = <value>; Eg: % global center group; % let center = applo; % let group = 100;
If we want to report macrovariable then we will use macrovariable reference (&) & % put statement. % put Statement: It can be used to print required text and macrovariable result in log window. Syntax: % put & <macrovariable (name)>; Eg: % put macro var value is & center; % put macro var value is & group; Eg: % macro cre; % global cname; % let cname = satyam; % mend; % cre; % put & cname;
We can create inside of the macroblock and we can use in only in current macroblock. - Local macrovariable values stores in local symbol tables. % local Statement: It can be used to create macrovariables. Eg: % macro loc; % local pid age; % let pid = p100; % let age = 45; % put & pid age is & age; % mend; % loc;
Automatic variables created by SAS & values assigned by SAS. It is a system defined User can use automatic macrovariables but user can't reassign any value to automatic macrovariables. Its values stores into a global symbol tables. Note: Global and local macrovariables, we can call as user defined macrovariables.
Argument: Macro arguments should be written in after the macroname within brackets. These arguments work like a local macrovariable. % macro print (danem); proc print data=&dname; run; % mend; /* calling */ % print (sasuser.demo); % print (sasuser.medi); % print (sashelp.buy); - We can pass the arguments in two ways.
It can be used to pass the arguments based on position. Ascending, descending, duplicate observation, duplicate variables. % macro sort (ename, new, s var); proc sort data = &ename out=&new' by & svar; run; % mend; % sort (demo, demo1, pid); %print (demo1); % sort (demo, demo2, descending pid); automatic macrovariables. Its values stores into a global symbol tables. Note: Global and local macrovariables, we can call as user defined macrovariables.
Argument: Macro arguments should be written in after the macroname within brackets. These arguments work like a local macrovariable. % macro print (danem); proc print data=&dname; run; % mend; /* calling */ % print (sasuser.demo); % print (sasuser.medi); % print (sashelp.buy); - We can pass the arguments in two ways.
It can be used to pass the arguments based on position. Ascending, descending, duplicate observation, duplicate variables. % macro sort (ename, new, s var); proc sort data = &ename out=&new' by & svar; run; % mend; % sort (demo, demo1, pid); %print (demo1); % sort (demo, demo2, descending pid); %print (demo2); % sort (demo, demo3, descending gender); %print (demo3);
data demo1; input pid age gender &; cards; 100 34 male 101 23 female 102 45 male 101 56 female ; /* calling */ % sort (demo1, demo2 nodupkey, pid); % print (demo2);
data lab; input pid test & units; cards; 100 hr 78 101 sbp 89 102 dbp 90 101 hr 70 ; /* calling */ % sort (lab, lab2 nodupkey, pid test units); % print (lab2);
Eg: % macro imp1 (file, dname, db); proc import datafile="file" out=&dname dbms=&db replace; run; % mend; /* calling */ % imp1 (d:\dataset.xls, demo1, excel); Eg: % macro imp2 (file, dname, db, sh); proc import datafile="file" out=&dname dbms=&db replace; sheet="&sh"; run; % mend; /* calling */ % imp2 (d:\dataset.xls, demo1, excel, medi); Eg: % macro imp3 (file, dname, db, sh, gt); proc import datafile="file" out=&dname dbms=&db replace; sheet="&sh"; getnames=> run; % mend; /* calling */ % imp3 (d:\dataset.xls, lab, excel, sheet1 & , gt); Eg: % macro imp4 (file, dname, db, sh, t, rg); proc import datafile="file" out=&dname dbms=&db replace; sheet="&sh"; getnames=> range="&rg"; run; % mend; /* calling */ % imp4 (d:\dataset.xls, demo, excel, demo, a1:g10); % imp4 (d:\dataset.xls, one, excel, demo, no, a10:g20);
% imp4 (d:\ base.xls, three (keep=subid age color), (excel, demo); part of variables & part of data % imp4 (d:\ base.xls, three (keep=subid age gender), (excel, demo, no, a20:e30);
% imp4 (d:\ base.xls, three (keep=f1 f3 f5) rename=(f1=stno f3=gender f5=color)), (excel, demo, no, a20:e30);
% macro acc1 (table, dname, db, file); proc import table=&tab out=&dname dbms=&db replace; database="&file"; run; % mend; /* calling */ % acc1 (labdata, lab, access, d:\data.mdb); % acc1 (labdata, lab1, (keep=pid units), access, d:\data.mdb); % acc1 (labdata, lab1, (drop=test), access, d:\data.mdb); % acc1 (labdata, lab2, (where=(test='hr')), access, d:\data.mdb);
It can be used to pass the arguments to macro by using parameters or argument names. Eg: % macro acc1(tab=1, dname= , db= , file= ); proc import table=&tab out=&dname dbms=&db replace; database="&file"; run; % mend; % acc1 (tab=labdata, dname=lab, db=access, file=d:\ data.mdb'); % acc1 (tab=labdata, dname=lab1, (keep=pid units), file=d:\ data.mdb, db=access); Note: If we want to use keyword and positional parameters in same application we must use below case only. positional first next keyword % macro imp4 (file, dname, db, sh= , gt= , rg= );
Using with macro quoting functions, we can mask the special characters at compilation time. These are of 2 types
Eg: % macro subset(new, ename, con); data &new; set &ename; where &con; run; % mend; % macro print(ename); proc print data= &ename; run; % mend; /* calling */ % subset (demo1, demofile, %str (color='B')); % print (demo1); /* calling */ % subset (demo2, demofile, age>25); % print (demo2); /* calling */ % subset (demo3, demofile, %nrstr (age>=25 & color='W')); % print (demo3); /* calling */ % subset (demo4, (keep=pid age race), demofile, %str (race='AF')); % print (demo4); (or) % subset (demo4, %str(demofile(keep=pid age race), %str (race='AF')); % print (demo4); /* calling */ % subset (demo5, (keep=pid age race), demofile, %str ((color='W')); % print (demo5); /* calling */ % subset (demo6, demofile(rename=(patid=subid)), %nrstr (race='AS' & color='W')); % print (demo6);
Eg: % macro sort(ename, new, svar); proc sort data=&ename out=&new; by &svar; run; % mend; % macro print(ename); proc print data= &ename; input pid age; run; cards; % mend; 100 90 % macro merge (new, enames, mvar); 101 89 data &new; ; merge &enames; data lab; by &mvar; input pid test & units; run; cards; % mend; 100 hr 89 /* calling */ 100 sbp 78 % sort (demo, demo1, pid); 101 hr 64 % sort (lab, lab1, pid); 101 dbp 56 % sort (dlab, demo1 lab1, pid); 100 sbp 85 % sort (dlab); 101 dbp 96 ;
Eg: data Uex; input stno uextype&; cards; 100 Eyedis 101 Eardis ; /* calling */ % sort (Uex, uex1, stno); % sort (sad, sad1, stno); % merge (Usad, % str)Uex(in=var1) sad1(in=var2)), % nrstr (stno; if var1=1 & var2=1)); % print (Usad);
/* calling */ % merge (usad1, % str(Uex(in=var1) sad1(in=var2)), % nrstr (stno; if var1=0 / var2=0)); % print (usad1);
/* calling */ % merge (usad2, % str(Uex sad1(in=var)), % nrstr (stno; if var1=0)); % print (usad2);
/* calling */ % merge (usad3, % str(Uex (in=var)sad1)), % nrstr (stno; if var=0)); % print (usad3);
proc sql; select * from Uex, sad where Uex stno=sad.stno; select * from Uex, sad on Uex stno=sad.stno; select * from Uex, sad inner join Uex stno=sad.stno; Note: If we want to use period between two macrovariable, instead of single period we should use two period(. .) because in macro single dot denotes default delimeter. % macro join (e1, e2, jo, con, mvar); proc sql; select 8 from &e1 &jo &e2 &con &e1 . . &mvar= &e2 . . &mvar; quit; % mend; % join (Uex, sad, %str(,), where, stno); - simple join % join (Uex, sad, inner join, on, stno); % join (Uex, sad, left join, on, stno); % join (Uex, sad, right join, on, stno); % join (Uex, sad, full join, on, stno);
% macro connect (db= , file= , tab= ); proc sql; connect to &db (&file); select * from connection to &db (select * from &tab); disconnect quit; % mend; /* calling */ - for excel % connect (db=excel, file=%str(path='d:\base.xls'), tab=[demo&]); /* for access */ % connect (db=access, file=%str(path='d:\base.mdb'), tab=rawdata 1); /* for oracle */ % connect (db=oracle, file=%str(User=scott password=tiger path='Ora'), tab=lab); 4) Macro Options: Macro options is a type of global options. Its default working whenever we run the macro application. - Macro options can be changed by using option statement. This statement should be written ouside of the macro block. - It dispalys a warning message in log window whenever macrocall is not resolved. - Using with this option, we can trace out required catalog(macrocall) existed or not. If we want to change nomerror. Default working is merror. More efficient is merror. Serror: Its default working and prints warning message whenever macro variable is not resolved. - If we want to change - noserror. Mprint: using Mprint option, we can trace out required macrocall to report errors in SAS coding. Default is noMprint. Eg: Opitons mprint; % connect (db=oracle, file=%str(User=scott password=tiger path='Ora'), tab=lab); Symbolgen option: It can be used to trace out macrovariable value (or) It prints message in logwindow how to resolve macrovariable. Default is nosymbolgen. Eg: Options mprint symbolgen; % connect (db=oracle, file=%str(User= scott password=tiger path='Ora'), tab=lab); Nested macros: If we write macroblock or macrocall inside of the another macroblock, then these macroblocks are called nested macros. Mprint nest options: It can be used to trace out nested macro with relation. Default is nomprintnest. Eg: % macro print (dname); proc print data= &dname; run; % mend; % macro sprint(ename, new, var); proc sort data= &ename out= &new; by &var; run; % print (&new); % mend; options mprintnest; % sprint(sasuser.medi, medi4, drug); 5) Macro Expressions: It requires operators and operands (var) . Eg: sale1 + sale2 sale > = 50 These are 3 types they are
a) Text expression: Macro coding is also called as text expression. b) Arthematic expression: It can be used to run arthematic operations in macros. i) %eval function: It can be used to do arthematic operations using macrovariables. % global var1 var2; % let var1 = 80; % let var2 = 90; % global var3; % let var3 = % eval (&var1+&var2); % put &var3; Note: % eval function temporarily convert macrovariable value character to numeric format. ii) % sysevalif function: If macrovariables have period of characters (float values) then we will use sysevalif for arthematic operations.
It requires operands (variable) Eg: int(var), length(var). In Macros, we can write functions inside or outside of the macroblock (global function).
% let dnames=demo lab medi adevent; % put Mvar length is % lengt (&dnames); /* storage */ % global len; % let len=%length (&dnames); b) % index: Using this function, we can report specific character position in macrovariable. Eg: /* Report */ % global dnames; % let dnames=demo lab medi adevent; % put char position is % index (&dnames, m); c) % scan: Using this function, we can get required word from string. Eg: /* Report */ % global dnames; % let dnames=demo lab medi adevent; % put Rq word is % scan (&dnames, 3); d) % Upcase: It shows required in capital letters. Eg: /* Report */ % global dnames; % let dnames=demo lab medi adevent; % put Rq string is % Upcase (&dnames); e) % Lowercase: It shows required data in small letters. Eg: % put Rq string is % Lowercase (&dnames); f) % substring: We can get part of the string from macrovariable. Here also internaly % eval is working. % put Rq string is % substr (&dnames, 6, 10); Note: % scan and % substr these two functions internally work with % eval function or expression.
Eg: % global surname name; % let surname=kolla; % let name=lava kumar; % global fname; % let fname=&surname &name; % put &fname;
Eg: % macro sprint (rq, ename, new, var); % if % Upcase(4rq)= SORT % then % do; proc sort data=&ename out=&new; by & var; run; % end; % else % do; proc print data = & ename; run; % end; % mend;
% sprint ( , sasuser.medi1); /* report */ % sprint (sort, sasuser.medi1, medi3, sbp); /*sorting */ % sprint ( , medi3); % sprint ( sort, sasuser.medi1, medi4, descending sbp); Eg: % macro dmanage (rq, dname, enames, var); % if % Upcase (&rq)=MERGE % then % do; data &dname; merge &enames; by & var; run; % end; % else % if % Upcase(&rq)=UPDATE % then % do; data &dname; Update &enames; by & var; run; % end; % else % if % Upcase(&rq)= MODIFY % then % do; data &dname; Modify &enames; by & var; run; % end; % else % do; proc sort data = &enames out = &dname; by & var; run; % end; % mend; % macro print (dname); proc print data = &dnmae; run; % mend; data medi; input pid drug 7 sbp; cards; 100 col5mg 156 101 col10mg 167 ; data success; input pid sbp; cards; 101 145 100 150 ; /* Calling */ % damange ( , medi1, medi, pid); % damange ( , success1, success, pid); % damange ( Update, medi2, medi1 success1, pid); % print (medi2); Eg: data emp1; input eid salary; cards; 100 2300 110 4500 230 5600 ; data emp2; input eid istage; informat istage percent4; cards; 230 30% 110 20% 100 10% ; options mprint; % damange ( , emp3, emp1, eid); % damange ( , emp4, emp2, eid); % damange (Modify, emp3, emp3 emp4, % str (eid, salary=salary+(salary * istage)); % print (emp3); Eg: data medi; input pid drug & sbp; cards; 100 col5mg 156 101 col10mg 167 ; data adevent; input pid adtype &; cards; 101 Eyedis 100 Eardis; % damange ( , medi1, medi, pid); % damange ( , adevent1, adevent, pid); % damange ( Merge, mead, medi1 adevent1, pid); % print (mead);
mlogic option: It can be used to trace out logical expressions. Default nomlogic % global dname; % let dnmae=emp1 emp2 emp3 emp4 medi; % macro down; % local dat 1; % let i=1; % do % while (&i <=5); % let dat=% scan (&dname, &i); proc print data =&dat; run; % let i =% eval (&i + 1); % end; % mend; options mprint symbologen mlogic; % do wh; Eg: % macro down1; % local dat 1; % let i=1; % let data=% scan (&dname, &i); - loop entering statement % do % while (&dat ne); proc print data =&dat; run; % let i =% eval (&i + 1); % let dat=%scan(&dname, &i); - loop running % end; % mend; options mprint symbologen mlogic; % do wh1;
This statement is working based on label statement and run group of required statements. Label Statement: This statement indicate group of statements. - If we want to run 'go to' statement we will use conditional block 'if'. Eg: % macro gto (rq, dname, enames, var); % if % Upcase (&rq) =SORT % then % goto sort; % else % if % Upcase (&rq)=MERGE % then % goto merge; % else % goto pront; % sort : proc sort data=&enames out = &dname; by & var; run; % goto ext; % print : proc print data=&dname; run; % goto ext; % merge : data &dname; merger &enames; by & var; run; % goto ext; % ext : % mend; /* calling */ % gto ( , sasuser.demo); /* reoprt */ % gto (sort, demo1, sasuser.demo, gender); /* sorting */ % gto ( , demo1); Eg: data demo; input pid age; cards; 100 78 101 90 ; data medi; input pid drug & ; cards; 101 5mg 100 10mg ; % macro dmanage (new, enames, var); data &new; & enames; by & var; run; % mend; % gto (sort, demo1, demo, pid); % gto (sort, medi1, medi, pid); % dmanage (dmedi, merge demo1 medi1, pid); % gto ( , dmedi); 6)Macro Interface Functions: Interface functions are 2 types.
Syntax: call symput ("Macrovariable"(name), dataset var name); Eg: data one; group =100; drug catalog; run; data_null_; set one; call symput ('var1', group); call symput ('var2',drug); run; opitons symbolgen; % put &var1 &var2;
Syntax: Symget9"Macrovariable"); Eg: data medi; Gid-symget ("var1"); Ndrug=symget("var2"); run; proc print data=medi; run; Eg: data medi2; input pid drug &; cards; 100 col5mg 101 col10mg 102 col15mg ; data_null_; set medi2; call symput ('var3', drug); run; % put 7var3; Note: If dataset has multiple values call symput function default stores last data value in macrovariable
Syntax: symexist ('Macrovariable'); Eg: data_null_; if symexist ('var6'); then put 'Macrovariable is existed'; else put 'Macrovariable does not exist'; run; - rawdata (sheet1) center pid age drug date group Appolo 100 27 5mg 12/23/2005 G100 Appolo 109 23 5mg 12/23/2005 G100 Appolo 108 34 10mg 12/24/2005 G100 Appolo 109 22 5mg 12/23/2005 G100 Appolo 118 45 15mg 12/25/2005 G100 Appolo 134 49 15mg 12/25/2005 G100 (sheet2) center group Age pid Appolo G200 26 190 Appolo G200 22 136 Appolo G200 37 156 Appolo G200 49 189 Appolo G200 43 123 Appolo G200 42 150 1st Import the data data_null_; set medi1; if age >=20 and age <=30 then do; call symput ('var1', drug); call symput ('D1', date); end; else if age > 30 and age <=40 then do; call symput ('var2', drug); call symput ('D2', date); end; else do; call symput ('var3', drug); call symput ('D3', date); end; run; % put &var1 &var2 &var3; % put &d1 &d2 %d3; data medi3; set medi2; if age >=20 and age <=30 then do; drug = symget ('var1'); date = symget ('D1'); end; else if age > 30 and age <=40 then do; drug = symget ('var2'); date = symget ('D2'); end; else do; drug = symget ('var3'); date = symget ('D3'); end; date1 = input (date, best12); drop date; rename date1 = date; run; proc print data = medi3; format date date9; footnote."Drug information &var1-&var2-&var3"; run;
Syntax: call execute ('%<macroname>');
% global varB; % let varB=89.67; % global varB1; % let varB1=%sysfnc(int(&varB)); % put &varB1; Dataset Functions:
Syntax: Exist ('Datasetname');
Eg: data_null_; of exist ('medi1')=1 then put 'dataset is existed'; else put 'dataset does not exist'; run;
Syntax: open ('datsetname');
Syntax: Attrn (open result, 'nvars' or 'nobs');
Syntax: close (open result); Eg: data_null_; if exist ('sasuser.demo')=1 then do; OP=open('sasuser.demo'); NV=attrn(OP, 'NVARS'); NO=attrn(OP, 'NOBS'); CL=close(OP); put 'dataset is existed'; put ' '; put ' '; put 'No of variables _ _'NV; put 'No of Obs _ _ _'NO; end; else put 'dataset does not exist'; run; (same coding in macros) % macro dex(dname); % if % sysfunC(exist(&dname))=1 % then % do; % local op nv nob cl; % let op=%sysfunc(open(&dname)); % let nv=%sysfunc(attrn(&op, nvars)); % let nob=%sysfunc(attrn(&op, nobs)); % let cl=%sysfunc(close(&op)); % put &dname dataset is existed with 7nv variables &nob observations; % end; % else; % put &dname dataset is not existed; % mend; % dex(sasuser.lab); % dex(sasuser.demo);
Query (or) select statement and into clause: Using these 2 options, we can create macrovariable from the dataset variable. Here SAS system default stores 1st data value (or) first occurence in macrovariable. Eg: data medi; input group & visit drug &; cards; G100 1 col5mg G200 1 col10mg G300 1 col15mg G100 2 col5mg G200 2 col10mg G300 2 col15mg ; proc sql noprint; select drug into : med 1 from medi quit; % put & med1;
Eg: proc sql noprint; select distinct(drug) into : medicine1-:medicine3 from medi; quit; % put &medicine1 &medicine2 &medicine3; titile "Drug information &medicine1 - &medicine2 - &medicine3"; proc sql; select * from medi; quit; Eg: proc sql noprint; select distinct(group) into : G1-: G3 from medi; quit; title1 "Group information is &G1 - &G2 - &G3"; title2 "Drug information &medicine1 - &medicine2 - &medicine3"; proc sql; select * from medi; quit;
_Global_: Using this statement, we can report list of the macrovariables (global macrovariables) with values. This statement can be written anywhere in SAS application. Eg: % global var1 var2 var3; % let var1 = 100; % let var2 = col5mg; % let var1 = 23; % put _global_; _Local_: Using this statement, we can report list of the local macrovariables. This statement can be written inside of the macroblock. Eg: MISSING LINE PG:216 % local pid age center; % let pid = 200; % let age = 45; % let center = appolo; % put _local_; % mend; %mvar; _User_: Using this statement, we can report user defined macrovariable. If we write inside of the macrobloxk, it reports both global; and local macrovariables. If we write outside of the macroblock, it report only global macrovariables. Eg: % put _User_; _Automatic_: Using this statement, we can report list of automatic macrovariable. Eg: % put _automatic_; Sysdate: It report current operating system date. Eg: options nodate; title1 'Drug information'; title2 "Report date-&sysdate9-&systime"; proc print data = saashelp.class; run; Systime: It report current operating system time (24hrs format) Syslast: It report recently existed dataset. Eg: data demo; x = 78; run; % put &syslast; - Default value of the syslast is _null_ Sysdsn: It report recently existed dataset & current working library. Eg: % put &sysdsn; Sysncpu: It report number of cpu's connected to pc. Eg: % put &sysncpu; Sysuserid: It report user id. Eg: % put &sysnuserid; Sysver: It report current SAS version. Eg: % put &sysver; Sysvlong: It report full of information for current SAS version. Eg: % put &sysnvlong; Note: If we use same name for global and local macrovariables, inside it works like a local macrovariable and outside it works like a global macrovariable. Eg: % global pid; % let pid = 100; % macro loc; % local pid; % let pid = 200; % put &pid; % mend; % loc; % put & pid;
Eg: % global pid; % let pid = 100; % macro loc; % let pid = 200; % put &pid; % mend; % loc; % put & pid; Sysmax: Using this statement, we can delete macrovariable from the SAS environment. Eg: % global pid age center; % let pid = 200; % let age = 34; % let center = appolo; % put _global_; % symdel age; % put _global_; Sysexec: using this function, we can manage operating system environment.
For storage: mstored SASMstore=<Autocall library>; Using these 2 options, we can store macro catalog in required library. Here SAS Mstore option indicate autocall library. Eg: Opitons mstored sasmstore = sasuser; % macro print (fname)/store; proc print data = &dname; run; % mend; Calling: SAS Autos: Using this options, we can indicate autocall library for calling. /* Calling */ Options sasautos = sasuser; % print (sasuser.demo);
Arrays: Using arrays concept, we can run same action for required variables. - Array statement starts with array keyword & requires name of the array, length of the array, variables list. Eg: data demo; input pid age gender & race & height weight; cards; 100 23 female Asian 4.5 56 101 23 male Asian 5.6 56 102 56 female Asian 5.6 56 103 23 female African 5.6 56 ; data demo1; <-- one dimensional explicit array set demo; array apple(3) age height weight; do i = 1 to 3; if apple(i) = then apple(i) = 0; end; array orange(2) & gender race; do i = 1 to 2; if orange(i) = ' ' then orange(i) = 'Miss'; end; drop i; run; proc print; run; - Arrays are 2 types
1) One Dimensional Array: is 2 types
Syntax: Array <Array name. <length of array> <data type> <variable list>;
Syntax: Array <Array name> <data type> <variable list>; Eg: data demo2; set demo; array apple age height weight; do over apple; if apple = then apple = 0; end; array orange & gender race; do over orange; if orange = ' ' then orange = 'Miss'; end; run; proc print data = demo2; run; - Explicitly array is more efficient than implicity array. - To run array, we should use special numeric and character varaibles.
It requires only one argument, the argument must be array name & it returns length of the array. Eg: data demo3; set demo; array apple(*) _numeric_; do i = 1 to dim(apple); if apple(i) = then apple(i) = 0; end; array orange(*) _char_; do i = 1 to dim(orange); if orange(i) = ' ' then orange(i) = 'Miss'; end; import the data into the table (sheet2)
proc import data file = 'd:\ condata.xls' out = two dbms = excel replace; sheet = 'sheet2&'; run; proc sql; describe table two; quit; To create a data set with not null constraint
proc sql; create table demo1 (pid num not null, age num, gender char, race char); quit; proc sql; describe table demo1; quit;
proc append base = demo1 data = two force; run; Check: Sheet3 Eg: pid age gender race 100 56 female Asian 101 45 female African 102 46 female African 103 34 male Asian Step1: Importing the data into SAS dataset name3 Step2: To create a table demo2 with check constraint.
Eg: proc sql; create table demo2 (pid num, age num, gender char, check(gender = 'female'), race char); quit; proc sql; describe table demo2; quit; /* Loading */ proc append base = demo2 data = three force; run;
Note: If we use multiple constraints in table, in loading time each and every observation must be satisfied all constraints in table. Raw data (sheet4): pid age gender race 100 56 female Asian 101 45 male African 101 46 female African 102 34 male Asian 103 female Asian 104 56 Asian 104 male African 105 45 female Asian Step1: Importing the data into SAS dataset name4 Step2: To create a table with multiple constraint for multiple variable. Eg: Step1 /* Extraction */ step2 /* transformation */ proc sql; create table demo4(pid num unique, age num not null, gender char not null, race char); quit; proc sql; describe table demo4; quit; drop i; run; proc print data = demo3; run;
You liked the article?
Like: 0
Vote for difficulty
Current difficulty (Avg): Medium
TekSlate is the best online training provider in delivering world-class IT skills to individuals and corporates from all parts of the globe. We are proven experts in accumulating every need of an IT skills upgrade aspirant and have delivered excellent services. We aim to bring you all the essentials to learn and master new technologies in the market with our articles, blogs, and videos. Build your career success with us, enhancing most in-demand skills in the market.
