BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
harrylui
Obsidian | Level 7

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

1 ACCEPTED SOLUTION

Accepted Solutions
7 REPLIES 7
ed_sas_member
Meteorite | Level 14

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;

 

ed_sas_member
Meteorite | Level 14

Here is the documentation about the DOSUBL() function:

https://documentation.sas.com/?docsetId=lefunctionsref&docsetTarget=p09dcftd1xxg1kn1brnjyc0q93yk.htm...

 

Hope this helps!

andreas_lds
Jade | Level 19

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"?

Patrick
Opal | Level 21

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;
harrylui
Obsidian | Level 7

thanks!

Tom
Super User Tom
Super User

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;

 

harrylui
Obsidian | Level 7

thanks

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
Mastering the WHERE Clause in PROC SQL

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.

Discussion stats
  • 7 replies
  • 703 views
  • 1 like
  • 5 in conversation