hi all,
here is my script
%Macro testing(A=,B=,C=);
proc sql;
create table &A as
select * from raw_file
where name like &B
and country in &C
;
quit;
%mend;
%Testing(A=RAW1,B="%Jasper%",C="Japan");
%Testing(A=RAW2,B="%Peter%",C="France");
%Testing(A=RAW3,B="%Ben%",C="England");
the problem is i have a long list from excel which contains people name and country
so do i have to type the name and country one by one under the %mend in SAS?
if i got 10000 records ,then should i input 10000 scripts in this SAS program?
can i just write a macro program so that the program can read the data from excel?
my excel format:
No . Name Country
raw1 jasper Japan
raw2 Peter France
raw3 Ben England
raw4 Harry USA
raw5 Henry China
Thank you all
Here is the documentation about the DOSUBL() function:
Hope this helps!
Hi @harrylui
Here is a way to achieve this.
NB: I suggest that you use the FIND() function in your proc sql to avoid issues with the %.
When you invoke the macro %testing, no need to use "" to specify the values of A, B and C.
Best,
/*UNTESTED CODE*/
/* Import your data from Excel - proc import */
data excel_file;
input No $ Name $ Country $;
datalines;
raw1 jasper Japan
raw2 Peter France
raw3 Ben England
raw4 Harry USA
raw5 Henry China
;
run;
%Macro testing(A=,B=,C=);
proc sql;
create table &A as
select * from raw_file
where find(upcase(name),upcase("&B"))
and find(upcase(country), upcase("&C"));
quit;
%mend;
%Testing(A=RAW1,B=Jasper,C=Japan);
/*Drive the macro execution througt data - DOSUBL function or CALL EXECUTE*/
data _null_;
set excel_file;
rc = dosubl(cats('%Testing(A=',No,', B=',Name,', C=',Country,')'));
run;
Here is the documentation about the DOSUBL() function:
Hope this helps!
The first thing you should check: do you really need a dataset for each row in your excel-file? What do you do with all those datasets? And what do you mean by "and country in &C"?
It doesn't feel right to create that many tables - but here you go.
The hash output method allows to create tables during execution time which makes this a simple task.
data have;
input (no name country) ($);
datalines;
raw1 jasper Japan
raw2 Peter France
raw3 Ben England
raw4 Harry USA
raw4 Peter USA
raw5 Henry China
12raw5 Henry China
;
proc sort data=have;
by no;
run;
data _null_;
if _n_=1 then
do;
dcl hash h1(dataset:'have(obs=0)', multidata:'y', hashexp:3);
h1.defineKey('name');
h1.defineData(all:'y');
h1.defineDone();
end;
set have;
by no;
h1.add();
if last.no then
do;
h1.output(dataset:no);
h1.clear();
end;
run;
thanks!
To generate code from data I find it much easier to generate the code to a text file. That way I can examine the file and make sure I have the generation logic correct. I can copy the first example from the file and run it to test it.
First let's make a dataset that looks like your Excel file.
data have;
input No_ :$32. Name &:$50. Country &:$50.;
cards;
raw1 jasper Japan
raw2 Peter France
raw3 Ben England
raw4 Harry USA
raw5 Henry China
;
Now we can write a data _null_ step to create the macro calls from that data.
filename code temp;
data _null_;
file code;
set have;
length a b c $100;
a=No_ ;
b=quote(cats('%',name,'%'),"'");
c=quote(trim(country));
put '%testing(' a= ',' b= ',' c= ');' ;
run;
So the resulting file will look like this:
%testing(a=raw1 ,b='%jasper%' ,c="Japan" );
%testing(a=raw2 ,b='%Peter%' ,c="France" );
%testing(a=raw3 ,b='%Ben%' ,c="England" );
%testing(a=raw4 ,b='%Harry%' ,c="USA" );
%testing(a=raw5 ,b='%Henry%' ,c="China" );
Then to run the generated calls just use %INCLUDE statement.
%include code / source2;
thanks
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.