DATA Step, Macro, Functions and more

Variables Using Count

Occasional Contributor
Posts: 6

Variables Using Count

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.;


proc sql; create table _&num. as select
&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."

Frequent Contributor
Posts: 130

Re: Variables Using Count

[ Edited ]

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=#

%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
					%else %do;
						&derivation_&i as &name_&i,
	from _events_
	where event_type="&num";




Contributor hbi
Posts: 66

Re: Variables Using Count



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

proc sql;
  select count(*) into :var_count 
  from edd_derivations 
  where event_type=&num.;
%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();



Super User
Posts: 17,840

Re: Variables Using Count

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.;
Ask a Question
Discussion stats
  • 3 replies
  • 4 in conversation