topic Re: Variables Using Count in SAS Programming

Variables Using Count

scott_darge — Tue, 29 Dec 2015 14:36:44 GMT

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;

Re: Variables Using Count

dcruik — Tue, 29 Dec 2015 19:01:51 GMT

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=&num;
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;

Re: Variables Using Count

hbi — Tue, 29 Dec 2015 19:24:51 GMT

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

Re: Variables Using Count

Reeza — Tue, 29 Dec 2015 20:02:55 GMT

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_&num;
set _events_;

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