DATA Step, Macro, Functions and more

Looping through Table to create Macro Variables

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 17
Accepted Solution

Looping through Table to create Macro Variables

 

Hello,

I am reading in a table below. I want to produce seperate datasets based on the Group_name and run some logic based on the Variable_name. Here is the syntax I am envisioning:

 

data &group_name.;

set Data;

if Variable in ( &Variable_name) then Value = 1; else Value = 2;

run;

 

So Group 1 dataset would look like:

data CRS; *Create dataset CRS;

set DATA;

if Variable in ('BAA', 'UST30Y', 'UST_30Y') then Value = 1; else Value = 2;

run;

 

Group 2:

data HPI; *Create dataset HPI;

set DATA;

if Variable in ('HPI') then Value = 1; else Value = 2;

run;

 

.. and so on for all 15 groups. In the end, I will have 15 seperate datasets with "group_names"

 

Does anyone have tips on how to loop through this table to get the correct Group Names and Variable Names for filtering?


Thanks

 

 

GroupGroup_NameVariable_Name
1CRSBAA
1CRSBaa_UST30Y
1CRSUST_30Y
2HPIBAC_HPI
3CCONFCCONF
4CREPICREPI
5EURO_UK_VAREUROSTOXX50_USD
5EURO_UK_VARNGDPGWUK_Y
5EURO_UK_VARRGDPGWEU_Q
6PCEGWPCEGW
7RDPIRDPI
8USGDPRGDPGW
9ASIA_VARRGDPGWCN_Y
9ASIA_VARRGDPGWIN_Y
9ASIA_VARRGDPGWJP_Y
10SP500SP500
11UNEMPUNEMP
12VIXVIX
13VSTOXXVSTOXX
14EURO_UK_UNEMPUNEMP_UK
15EURO_UK_GDPRGDPGWUK_Q

Accepted Solutions
Solution
‎07-25-2016 11:27 AM
Occasional Contributor
Posts: 17

Re: Looping through Table to create Macro Variables

Posted in reply to Astounding

I'm not too sure a BY processing step would solve my issue.

 

I was able to accomplish what I wanted using the SAS code below. Please let me know if you have any suggesstions

proc sql noprint;
select
max(GROUP)
into
:MAX_GROUP_NUM
from MEF_GROUPS_INDEX;
quit;

%macro loop;
%do i =1 %to &MAX_GROUP_NUM;

proc sql noprint;
select DISTINCT
GROUP_NAME,
"'"|| TRIM(VARIABLE_NAME) ||"'"
into
:GROUP_NAME,
:VARIABLE_NAME SEPARATED BY ","
from MEF_GROUPS_INDEX
where GROUP_NUM = &i.;
quit;

data attrtemp.ATTR_&GROUP_NAME.;
set JOINED_ATTR;
if VARIABLE_CODE in (&VARIABLE_NAME.) then ATTR_VALUE = SCENARIO_ATTR;
	 else ATTR_VALUE = SCENARIO;
run;

%end;
%mend loop;
%loop

data Attribution_Combined;
set attrtemp.attr_ :;
run;

View solution in original post


All Replies
SAS Super FREQ
Posts: 8,868

Re: Looping through Table to create Macro Variables

Hi:

  The first rule of thumb when writing a macro solution is to start with a working SAS program and then work backwards from there. Consider these 2 versions of the program:

 

** start with a working SAS Program;
 ** One possibility;
data group1;
  set sashelp.class;
  if name in ('Alfred' 'Alice' 'Barbara') then value=1;
     else value=2;
run;
   
proc print data=group1;
  title 'Every obs from input is written to GROUP1 data file';
run;

data group2;
  set sashelp.class;
  if name in ('Robert' 'William' 'Janet') then value=1;
     else value=2;
run;
  
proc print data=group2;
  title 'Every obs from input is written to GROUP2 data file';
run;
    
** Alternate version;
data group1_alt;
  set sashelp.class;
  if name in ('Alfred' 'Alice' 'Barbara') then do;
    value=1;
	output;
  end;
run;
   
proc print data=group1_alt;
  title 'Only 3 students are written to GROUP1_ALT data file';
run;

    
** Alternate version;
data group2_alt;
  set sashelp.class;
  if name in ('Robert' 'William' 'Janet') then do;
    value=1;
	output;
  end;
run;
   
proc print data=group2_alt;
  title 'Only 3 students are written to GROUP2_ALT data file';
run;

  

In the first set of programs, GROUP1 and GROUP2 output files both have a total of 19 observations, with different obs having different values for the variables.

 

In the "_ALT" version of the programs, the only rows that are output are the rows that meet the IF condition. So before anyone undertakes to "macroize" your code, we need to understand a few things:

1) which logic do you want to follow;

2) how will &Varible_name get changed for each invocation of the macro program;

3) how will &GROUP_NAME get changed for each invocation of the macro program

4) will this be data driven (a macro %DO loop that will iterate 15 times? In which case, if the macro is data driven, then won't &VARIABLE_NAME be a numbered macro variable like &VARNAME1, &VARNAME2...etc until &VARNAME15??

 

cynthia

Super User
Posts: 19,868

Re: Looping through Table to create Macro Variables

Are you familiar with BY processing in SAS? It usually makes this unncessary. 

 

For example if you want summary statistics BY group, you can add BY GROUP variables to the proc and the resulsts are done for each group individually. Many procs have this feature.

 

In 90% of the cases I've seen, splitting a dataset isn't required and is inefficient compared to other alternatives.

Super User
Posts: 5,516

Re: Looping through Table to create Macro Variables

To add some context to what Reeza said, there is no need to create VALUE=1 or VALUE=2.  Instead, create GROUP_NAME using exactly the values that you show in your original post.  It will be easy to select a particular GROUP_NAME value later, and it will be easy to understand what the GROUP_NAME values represent.

Solution
‎07-25-2016 11:27 AM
Occasional Contributor
Posts: 17

Re: Looping through Table to create Macro Variables

Posted in reply to Astounding

I'm not too sure a BY processing step would solve my issue.

 

I was able to accomplish what I wanted using the SAS code below. Please let me know if you have any suggesstions

proc sql noprint;
select
max(GROUP)
into
:MAX_GROUP_NUM
from MEF_GROUPS_INDEX;
quit;

%macro loop;
%do i =1 %to &MAX_GROUP_NUM;

proc sql noprint;
select DISTINCT
GROUP_NAME,
"'"|| TRIM(VARIABLE_NAME) ||"'"
into
:GROUP_NAME,
:VARIABLE_NAME SEPARATED BY ","
from MEF_GROUPS_INDEX
where GROUP_NUM = &i.;
quit;

data attrtemp.ATTR_&GROUP_NAME.;
set JOINED_ATTR;
if VARIABLE_CODE in (&VARIABLE_NAME.) then ATTR_VALUE = SCENARIO_ATTR;
	 else ATTR_VALUE = SCENARIO;
run;

%end;
%mend loop;
%loop

data Attribution_Combined;
set attrtemp.attr_ :;
run;
Occasional Contributor
Posts: 17

Re: Looping through Table to create Macro Variables

I'm not too sure a BY processing step would solve my issue.

 

I was able to accomplish what I wanted using the SAS code below. Please let me know if you have any suggesstions

 

proc sql noprint;
select
max(GROUP)
into
:MAX_GROUP_NUM
from MEF_GROUPS_INDEX;
quit;

%macro loop;
%do i =1 %to &MAX_GROUP_NUM;

proc sql noprint;
select DISTINCT
GROUP_NAME,
"'"|| TRIM(VARIABLE_NAME) ||"'"
into
:GROUP_NAME,
:VARIABLE_NAME SEPARATED BY ","
from MEF_GROUPS_INDEX
where GROUP_NUM = &i.;
quit;

data attrtemp.ATTR_&GROUP_NAME.;
set JOINED_ATTR;
if VARIABLE_CODE in (&VARIABLE_NAME.) then ATTR_VALUE = SCENARIO_ATTR;
	 else ATTR_VALUE = SCENARIO;
run;

%end;
%mend loop;
%loop

data Attribution_Combined;
set attrtemp.attr_ :;
run;
Super User
Posts: 19,868

Re: Looping through Table to create Macro Variables

Looking at your code I believe this can be simplified. 

 

Can you post sample input and output data?

Occasional Contributor
Posts: 17

Re: Looping through Table to create Macro Variables

Hi Reeza,

Please see attached JOINED_ATTR table, MEF_GROUP_INDEX table, and an example output table which will be called ATTR_EURO_UK_VAR according to my code.

 

Let me know if you have any tips

 

Thanks

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 1397 views
  • 1 like
  • 4 in conversation