Introduction to MACROS in SAS

Ratings:
(4)
Views:0
Banner-Img
  • Share this blog:

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

  1. Macro compiler or Processor
  2. Macro language

Macroprocessor:

This is one of the part in SAS.  

Macrolangauge:

It can be used interact with macroprocessor.  

Macrotiggers (%,&):

It can be used to identify macrolanguage.  

Percentage (%):

This is called macro reference Each and every macro statement starts with %.  

Ampson(&):

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.  

Syntax:

% 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.  

Concepts In Macro Language:

  • Macro variables creation
  • Passing arguments to macros
  • Macro quoting function
  • Macro options.
  • Macro expressions
  • Macro interface functions.

 

Macro Variables Creation:

Macrovariables are 3 types

  • Global macrovariable
  • Local macrovariable
  • Automatic macrovariable

- 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;  

Macrovariable reference:

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;  

b) Local macrovariable creation:

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;  

c) Automatic macrovariables creation:

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.  

  1. Passing Arguments to Macros:

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.

  1. Positional parameter or arguments.
  2. Keyword parameter.

 

1) Positional parameter:

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.  

  1. Passing Arguments to Macros:

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.

  1. Positional parameter or arguments.
  2. Keyword parameter.

 

1) Positional parameter:

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);  

/* To report duplicate data values and variables */

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);  

/* To climinate duplicate observations */

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);  

/* import procedure */

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=&gt; 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=&gt; range="&rg"; run; % mend; /* calling */ % imp4 (d:\dataset.xls, demo, excel, demo, a1:g10); % imp4 (d:\dataset.xls, one, excel, demo, no, a10:g20);  

/* To import required variables */

% 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);  

/* To rename the variables names */

% imp4 (d:\ base.xls, three (keep=f1  f3  f5) rename=(f1=stno  f3=gender  f5=color)), (excel, demo, no, a20:e30);  

/* To develop macro application for access file */

% 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);  

2) Keyword parameters:

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=  );  

3) Macro Quoting functions:

Using with macro quoting functions, we can mask the special characters at compilation time. These are of 2 types

  1. % str
  2. % nrstr
  3. % str: Using this function, we can mask all special characters except macro triggers and unmatched quotations and unmatched brackets.
  4. % nrstr: Using this function, we can mask all special characters include macro triggers at compilation time.

 

/* To develop macro application for subset of data */

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);  

/* To develop merge application in macros */

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                                                                      ;  

/* To report matching data */

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);  

/* To report non-matching data from 2 datasets */

/* calling */ % merge (usad1, % str(Uex(in=var1) sad1(in=var2)), % nrstr (stno; if var1=0  /  var2=0)); % print (usad1);  

/* To report non-matching data from hte required dataset(Uex) */

/* calling */ % merge (usad2, % str(Uex sad1(in=var)), % nrstr (stno; if var1=0)); % print (usad2);  

/* To report non-matching data from the sad */

/* calling */ % merge (usad3, % str(Uex (in=var)sad1)), % nrstr (stno; if var=0)); % print (usad3);  

/* To develop joins coding in macros */

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);  

/* To develop macro application with pass through totally */

% 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

  1. Text expressions
  2. Arithematic expressions
  3. logical expressions

  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.

  1.                 % global var  var5;% let var4 = 80.20;% let var5 = 90.30;% global var6;% let var6 =  % sysevalif (&var4+&var5);% put  &var6;

   

6) Macro Functions (or) String Functions

It requires operands (variable) Eg: int(var), length(var). In Macros, we can write functions inside or outside of the macroblock (global function).

  1. Eg:          /* Report */% global dnames;a) % length: Using this function, we can report length of the macrovariable.

% 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.    

Concatination Of Macrovariables:

Eg:          % global surname name; % let surname=kolla; % let name=lava kumar; % global fname; % let fname=&surname  &name; % put  &fname;  

5) e) Logical Expressions:

  1. % if % then   % else
  2. % if % then  % else  % if ...... % else

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;

/* Calling */

% 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);  

% Do % while loop:

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;  

Go To Block (or) Statement:

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.

  1. Dataset (or) datastep interface functions
  2. Interface functions.
  3. a)Dataset Interface Function: Using interface functions, we can call required macro functions during data step execution,
  4. a) Call symput function: It is a call routine (function). Using this function, we can create macrovariables from the dataset variables during during datastep execution.

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;  

  1. b) Symget function: Using this function, we can create dataset variables from the macrovariables during datastep execution.

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

  1. C) Symexist function: Using this function, we can report required macrovariable existed or not. This function we can run by using condition.

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;

  1. d) cal execute: Using call execute function, we can call required catalog (macrocall) from th dataset block.

Syntax: call execute ('%<macroname>');

  1. Interface functions:

  2. a) %sysfunC: Using this function, we can call dataset functions in macros.

% global varB; % let varB=89.67; % global varB1; % let varB1=%sysfnc(int(&varB)); % put  &varB1; Dataset Functions:

  1. a) exist: Using this function, we can report required SAS file is existed or not. If it is existed it returns '1' otherwise 'Zero'.

Syntax: Exist ('Datasetname');

  1. b) file exist: Using this function, we can report pc files are existed or not. If it is existed it returns '1' otherwise 'zero'.

Eg:          data_null_; of exist ('medi1')=1 then put 'dataset is existed'; else put 'dataset does not exist'; run;

  1. c) Open Function: Using this function, we can open the required dataset internally.

Syntax: open ('datsetname');

  1. d) Attrn: Using this function, we can count no of variables and no of observations using open results.

Syntax: Attrn (open result, 'nvars' or 'nobs');

  1. e) close: Using this function, we can close the dataset.

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);

/* To create a macrovariable with sql block */

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;

/* To create multiple macrovariables */

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;

To report list of macrovariables:

_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;  

/* To reassign global macrovariable */

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.    

To store macros permanently:

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);  

SAS Base:

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
  2. Two Dimensional Array

1) One Dimensional Array: is 2 types

  1. Explicit Array
  2. Implicit Array
  3. a) Explicit Array: It is working based on length of the array & loop variables.

Syntax: Array <Array name. <length of array> <data type> <variable list>;

  1. b) Implicit Array: It is working based on array names with do over statement.

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.  

Dim Function:

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)

/* Extraction */

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

/* Transformation */

proc sql; create table demo1 (pid num not null, age num, gender char, race char); quit; proc sql; describe table demo1; quit;

/* Loading */

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.

/* Transformation */

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;  

Multiple constraints for multiple variable in table:

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

Recommended Courses

1/15

About Author
Authorlogo
Name
TekSlate
Author Bio

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.


Stay Updated


Get stories of change makers and innovators from the startup ecosystem in your inbox