topic Re: Doubts on manipulating dataset and nest it on a macro in SAS Programming

Doubts on manipulating dataset and nest it on a macro

rmlmrmlm — Wed, 16 Aug 2017 14:06:19 GMT

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!

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

ballardw — Wed, 16 Aug 2017 14:27:53 GMT

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.

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

PaigeMiller — Wed, 16 Aug 2017 14:29:03 GMT

No macro needed.

Something like

if var_income_to_update='INCOME1' then ... ;
else if var_income_to_update='INCOME2' then ...;

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

Miracle — Wed, 16 Aug 2017 14:49:59 GMT

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;

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

Ksharp — Wed, 16 Aug 2017 14:54:12 GMT


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;