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();
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.