topic Re: Need help with proc sql and macro in SAS Programming

Need help with proc sql and macro

Reader587 — Mon, 20 Mar 2023 04:41:20 GMT

Hello all, I have this code and I want to create 3 different tables using a macro statement. Need some help. I can simply change &type1 to &type2 but what if I got 12 groups, what is an easier way to just do all 3? can I use a %do %to statement?

proc sql number; create table complete_storm as
select Name,upcase(Basin) as Basin,maxwind,startdate,maxwind*1.60934 as maxwindkm,
startdate-enddate as stormlength,
case
when substr(Basin,2,1) eq "I" then "Indian"
when substr(Basin,2,1) eq "A" then "Atlantic"
else "Pacific"
end as Ocean
from forth.storm_summary_small
where Name is not missing;
quit;
proc sql;select distinct ocean /* Creating 3 macros */
into: type1- from complete_storm;quit;
%put &=type1;%put &=type2;%put &=type3;

proc sql;create table &type1 as
select * from complete_storm
where ocean eq "&type1";quit;

Re: Need help with proc sql and macro

Kurt_Bremser — Mon, 20 Mar 2023 06:10:44 GMT

Why do you need to split the data in the first place? You can always use

by ocean;

(after sorting) in procedure and data steps to do analysis per group.

Re: Need help with proc sql and macro

Kurt_Bremser — Mon, 20 Mar 2023 06:17:55 GMT

You can use %DO if you create a variable with a list instead of a list of variables:

proc sql noprint;
select distinct ocean unto :types separated by " "
from complete_storm;
quit;

%macro process_list;
%local i type;
%do i = 1 %to %sysfunc(countw(&list.));
  %let type = %scan(&list.,&i.);
  /* process individual group stored in &type */
%end;
%mend;
%process_list /* call the macro */

but, as already stated, this is not necessary in 99% of cases.

Re: Need help with proc sql and macro

PaigeMiller — Mon, 20 Mar 2023 09:58:16 GMT

There are no macros in your code. There are macro variables. Macros are not macro variables, the two should not be confused.