Hi All,
I'm looking to create a number of different tables which are broadly similar but have a different number of additional variable which are calculated. So standard_vars will always be standard but the count of other variables I'd like determined on the select (*) from the first statement. I.e. if count(*) is 2 then I'd only have 2 additional variables in the create table statement. Would anybody be able to help me limit the variables in the second statement? Any help would be greatly appreciated.
proc sql; select count(*) into :var_count from edd_derivations where event_type=&num.;
quit;
proc sql; create table _&num. as select
&standard_vars.,
&derivation_1. as &name_1.,
&derivation_2. as &name_2.,
&derivation_3. as &name_3.,
&derivation_4. as &name_4.
from _events_
and event_type="&num."
;quit;
Just to help me understand a little more, in your SQL procedure, the &derivation_1. as &name_1. through &derivation_4. as &name_4. are the additional variables you want? So, in the case you presented, the var_count macro variable would resolve to 4? If that is the case, then I believe the below code would create as many columns called "&name_n" as the value of the var_count macro (in the case you presented, it would list 4 fields). Also, this is obviously assuming that you have values for the macro variables num, derivation, and name being called.
Hope this helps, if it's not what you're looking for, maybe give a better example of the data you have and what you'd like to see.
Also, just an observation, but you have the event type as a character format in the _events_ data set and a numeric format in the edd_derivations data set. Was that intended, or do you need to add/remove "" from one of the SQL procedures?
proc sql noprint;
select count(*) into: var_count
from edd_derivations
where event_type=#
quit;
%macro create;
proc sql;
create table _&num as
select &standard_vars,
%do i=1 %to &var_count;
%if &i=&var_count %then %do;
&derivation_&i as &name_&i
%end;
%else %do;
&derivation_&i as &name_&i,
%end;
%end;
from _events_
where event_type="&num";
quit;
%mend;
%create;
Hi,
This response is pretty similar to dcruik's response (I think we both started responding around the same time).
In any case, I added some sample data. Also, placing the comma before the derivation_1, derivation_2, etc. statement is handy in that it avoids the need to add an "%else %do" statement. Enjoy.
%let num = 4; *fictitious value;
%let standard_vars = %STR(abc, xyz); *fictitious variables;
/* fictitious dataset to mimic yours */
data edd_derivations;
do i=1 to 200;
event_type = round(ranuni(123)*25);
output;
end;
run;
proc sql;
select count(*) into :var_count
from edd_derivations
where event_type=&num.;
quit;
%put &var_count;
/* fictitious dataset to mimic yours */
data _events_;
length event_type abc xyz $8;
do j=1 to 500;
event_type = cats(round(ranuni(456)*25), "");
abc = byte(65 + mod(j, 26));
xyz = byte(90 - mod(j, 26));
output;
end;
run;
/* this version, "DynamicSQL1()", shows the general approach */
%MACRO DynamicSQL1();
proc sql;
create table _&num. as
select &standard_vars.
%IF %EVAL(&var_count > 0) %THEN %DO;
%DO k=1 %TO &var_count;
, CASE WHEN /* some condition goes here [e.g. " abc in ('A','B','C')" ] */ THEN ':-)' ELSE ':( ' END AS name_&k.
%END;
%END;
from _events_
where event_type="&num."
;
quit;
%MEND;
%DynamicSQL1();
/* this version, "DynamicSQL2()", is intended to mimic your example;
I wasn't sure if your derivation_1, derivation_2, etc. variables
were dynamic or stayed constant throughout */
%MACRO DynamicSQL2();
/* these macro variables are silly placeholders;
please replace with your own logic */
%let derivation_1 = %str(CASE WHEN 1=2 THEN 'sad' ELSE 'happy' END);
%let derivation_2 = %str(CASE WHEN 2=3 THEN 'sad' ELSE 'happy' END);
%let derivation_3 = %str(CASE WHEN 3=4 THEN 'sad' ELSE 'happy' END);
%let derivation_4 = %str(CASE WHEN 4=5 THEN 'sad' ELSE 'happy' END);
%let derivation_5 = %str(CASE WHEN 5=6 THEN 'sad' ELSE 'happy' END);
proc sql;
create table _&num. as
select &standard_vars.
%IF %EVAL(&var_count > 0) %THEN %DO;
%DO k=1 %TO &var_count;
/* note the double ampersand before the derivation variable ... */
, &&derivation_&k. AS name_&k.
%END;
%END;
from _events_
where event_type="&num."
;
quit;
%MEND;
%DynamicSQL2();
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!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.