BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Terho
Obsidian | Level 7

 

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
1 ACCEPTED SOLUTION

Accepted Solutions
Terho
Obsidian | Level 7

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

7 REPLIES 7
Cynthia_sas
SAS Super FREQ

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
Super User

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
PROC Star

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
Obsidian | Level 7

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
Obsidian | Level 7

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
Super User

Looking at your code I believe this can be simplified. 

 

Can you post sample input and output data?

Terho
Obsidian | Level 7

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

 

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
  • 7 replies
  • 10123 views
  • 1 like
  • 4 in conversation