topic Re: Consolidating two steps into one in SAS Procedures

Consolidating two steps into one

Neil_C — Thu, 16 Jul 2015 12:27:35 GMT

I want to combine these two steps below into one by creating the variable Value within the Master dataset itself, without using proc sort to get rid of the duplicates. Is there multiple ways of doing that?

proc sort data=master out=test nodupkey;

where prefix = 'ABC';

by id group;

run;

data test;

set test;

value = 1.0;
run;

Re: Consolidating two steps into one

Steelers_In_DC — Thu, 16 Jul 2015 12:43:47 GMT

Here you go, this way the 1.0 is a character. If you want it to be a number remove the quotes:

data master;

infile cards dsd;

input id$ group$ prefix$;

cards;

100,abc,ABC

200,abc,123

300,abc,123

400,qwe,123

500,qwe,ABC

;

run;

proc sql;

create table test as

select distinct *,'1.0' as Value

from master

where prefix = 'ABC'

order by id,group;

Re: Consolidating two steps into one

Neil_C — Thu, 16 Jul 2015 13:30:59 GMT

Thanks Mark for the proc sql method. I have more variables in this file where I was aiming to create different values for Value based on multiple if ... then else conditions. So it would be nice to change the master file instead of creating another file 'test'. Is there a similar program like below?

So the program would be something like

data master;

set master;

if condition1 then value = 2;

else if condition2 then value = 3;

else if ......;

....;

else if prefix = 'ABC' ..... then value = 1;

run;

Re: Consolidating two steps into one

Ksharp — Thu, 16 Jul 2015 13:34:15 GMT

Hash Table can do that.

Re: Consolidating two steps into one

Steelers_In_DC — Thu, 16 Jul 2015 13:40:18 GMT

I think it would be best to provide a sample of the data you have and give more information for what you want.

Re: Consolidating two steps into one

Neil_C — Thu, 16 Jul 2015 14:11:58 GMT

data master;

infile cards dsd;

input id$ group$ prefix$ var1$ var2;

cards;

100,abc,ABC,F,11

100,abc,123,F,11

200,abc,123,H,12

300,abc,123,F,11

400,qwe,123,H,13

500,qwe,ABC,F,15 ;

run;

data master;

set master;

if prefix = 'ABC' then value = 1; * need to insert appropriate program here that scans through rows vertically for each combination of id & group, and labels Value as 1 for row containing ABC and ignores other rows. Only one row should have a value of 1 if there are multiple mentions of ABC in prefix for each id-group combination.

For example, for 1st two rows combination of id-group (100 & abc) mentioned above, only 1st row gets assigned Value as 1 and next row as missing. For the rest of rows not having prefix values of ABC the program is shown below.

Hope this makes sense ;

else if var1 = 'F' and var2 =11 then value = 2;

else if var1 = 'H' and var2 =11 then value = 3;

else if ......;

else if ....;

run;