BookmarkSubscribeRSS Feed
scott_darge
Calcite | Level 5

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;

3 REPLIES 3
dcruik
Lapis Lazuli | Level 10

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;

 

 

hbi
Quartz | Level 8 hbi
Quartz | Level 8

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();

 

 

Reeza
Super User
Where do &derivation_1 and &name_1 get declared/assigned? Is there a naming convention to the variables? I wonder if a data step would be more efficient if there was a naming convention. For example if the variables are listed in the dd_derivations list that could be queried as well.

data table_#
set _events_;

keep &standard_vars &derivation_1-&derivation_&var_count;
rename &derivation_1-&derivation_&var_count=&name1-&&name_&var_count.;
run;

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 946 views
  • 0 likes
  • 4 in conversation