Macro questions on excel

[harrylui]

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

[ed_sas_member]

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 · Accepted solution]

Here is the documentation about the DOSUBL() function:

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

Hope this helps!

[andreas_lds]

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]

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]

thanks!

[Tom]

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]

thanks