Looping through Table to create Macro Variables

[Terho]

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

Group	Group_Name	Variable_Name
1	CRS	BAA
1	CRS	Baa_UST30Y
1	CRS	UST_30Y
2	HPI	BAC_HPI
3	CCONF	CCONF
4	CREPI	CREPI
5	EURO_UK_VAR	EUROSTOXX50_USD
5	EURO_UK_VAR	NGDPGWUK_Y
5	EURO_UK_VAR	RGDPGWEU_Q
6	PCEGW	PCEGW
7	RDPI	RDPI
8	USGDP	RGDPGW
9	ASIA_VAR	RGDPGWCN_Y
9	ASIA_VAR	RGDPGWIN_Y
9	ASIA_VAR	RGDPGWJP_Y
10	SP500	SP500
11	UNEMP	UNEMP
12	VIX	VIX
13	VSTOXX	VSTOXX
14	EURO_UK_UNEMP	UNEMP_UK
15	EURO_UK_GDP	RGDPGWUK_Q

[Cynthia_sas]

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

[Reeza]

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.

[Astounding]

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.

[Terho · Accepted solution]

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;

[Terho]

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;

[Reeza]

Looking at your code I believe this can be simplified. 

Can you post sample input and output data?

[Terho]

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