Doubts on manipulating dataset and nest it on a macro

Reply
Contributor
Posts: 23

Doubts on manipulating dataset and nest it on a macro

[ Edited ]

Hello everybody!

I need help, please, as I'm a newbie to SAS.

 

Imagine this dataset DATASET_ORIGINAL, with the variables:

 

ID    INCOME1    INCOME2       INCOME3            VAR_INCOME_TO_UPDATE            NEW_INCOME_VALUE

-----------------------------------------------------------------------------------------------------------------------------------------------------

1          100              200                   150                              INCOME2                                            201

2          400              250                   250                              INCOME1                                              99

3          300              210                   165                              INCOME2                                            188

3          800              320                   155                              INCOME2                                            178

 

I want to create a new DATASET_COPY, dinamically, by updating, for each ID, the variable whose name is in VAR_INCOME_TO_UPDATE with the NEW_INCOME_VALUE.

 

The final DATASET_COPY will be:

ID    INCOME1    INCOME2       INCOME3           

------------------------------------------------------------------

1          100              201                   150                

2            99              250                   250                

3          300              188                   165                

3          800              178                   155                

 

This is legacy ... And I must keep the logic.

 

 Thank you very much!

Super User
Posts: 12,148

Re: Doubts on manipulating dataset and nest it on a macro

Here's one way.

Note the example data in the form of a data step so that code can be tested.

data have;
   input ID    INCOME1    INCOME2       INCOME3            VAR_INCOME_TO_UPDATE $           NEW_INCOME_VALUE ;
datalines;
1          100              200                   150                              INCOME2                                            201
2          400              250                   250                              INCOME1                                              99
3          300              210                   165                              INCOME2                                            188
3          800              320                   155                              INCOME2                                            178
;
run;

data want;
   set have;
   array t (3) $8 _temporary_ ('INCOME1', 'INCOME2', 'INCOME3');
   array inc Income1-income3;
   inc(whichc(VAR_INCOME_TO_UPDATE, of t(*)))=New_income_value;
run;

The array t holds the list of values of interest and the array Inc has to have the variables listed in the same order as t.

 

the WhichC function finds which character value has the text of interest and returns the number in position order, so can be used as the array inex for Inc.

 

I'll leave any drop statements to you.

Respected Advisor
Posts: 2,153

Re: Doubts on manipulating dataset and nest it on a macro

No macro needed.

 

Something like

 

if var_income_to_update='INCOME1' then ... ;
else if var_income_to_update='INCOME2' then ...;
--
Paige Miller
Regular Contributor
Posts: 236

Re: Doubts on manipulating dataset and nest it on a macro

or even this?

 

data DATASET_ORIGINAL;
input ID INCOME1 INCOME2 INCOME3 VAR_INCOME_TO_UPDATE $ NEW_INCOME_VALUE;
datalines;
1 100 200 150 INCOME2 201
2 400 250 250 INCOME1  99
3 300 210 165 INCOME2 188
3 800 320 155 INCOME2 178
;
run;
data DATASET_COPY(keep=id income:); 
	set DATASET_ORIGINAL;
	array INCOME{3};
	do i=1 to 3; 
		if VAR_INCOME_TO_UPDATE=vname(INCOME{i}) then INCOME{i}=NEW_INCOME_VALUE;
	end;
run;
proc print noobs; run;

 

Super User
Posts: 10,313

Re: Doubts on manipulating dataset and nest it on a macro


data have;
   input ID    INCOME1    INCOME2       INCOME3            VAR_INCOME_TO_UPDATE $           NEW_INCOME_VALUE ;
datalines;
1          100              200                   150                              INCOME2                                            201
2          400              250                   250                              INCOME1                                              99
3          300              210                   165                              INCOME2                                            188
3          800              320                   155                              INCOME2                                            178
;
run;

data want;
   set have;
   array x{*} Income1-income3;
   do i=1 to dim(x);
    if upcase(VAR_INCOME_TO_UPDATE)=upcase(vname(x{i})) then do;
      x{i}=NEW_INCOME_VALUE;leave;
	end;
   end;
   drop i;
run;

Ask a Question
Discussion stats
  • 4 replies
  • 121 views
  • 0 likes
  • 5 in conversation