topic Re: SAS dataset modification by using variable values in SAS Programming

SAS dataset modification by using variable values

jayakumarmm — Sat, 10 Sep 2016 18:39:16 GMT

I need to modfiy my dataset based on specifc variables values. Given below is the dataset

Please share your thoughts on this

Logic: Consider only the highlighted portion of the current dataset and drop the remaining variable values for creating a new dataset

Current dataset:

COLUMN1 COLUMN2 COLUMN3
TEAM
CLASS
DEPT
CUST1 YES
AGE 45
MANA2 YES
AGE_1 60
EMPLOY3 NO
AGE_2 90
LOC
GEO
STATE

Expected dataset

COLUMN1 COLUMN2 COLUMN3
CUST1 YES 45
MANA2 YES 60
EMPLOY3 NO 90

Re: SAS dataset modification by using variable values

Reeza — Sat, 10 Sep 2016 18:41:29 GMT

Did you read this dataset from a text file?

If so, it would be easier to modify the read in process.

Convert excel data into SAS dataset in transposed format

jayakumarmm — Sat, 10 Sep 2016 22:53:25 GMT

I am struggling for 4 to 5 hrs to build a dataset in SAS , I have attached the current and expected dataset. Now I am need of expertise advise.

Re: SAS dataset modification by using variable values

Astounding — Sat, 10 Sep 2016 22:56:23 GMT

if you can rely on the structure of the incoming data, here's a way to go about it:

data want;

do i=1 to 2;

set have;

if column1 in ('TEAM', 'CLASS', 'DEPT', 'LOC', 'GEO', 'STATE') then delete;

if i=1 then do;

col1 = column1;

col2 = column2;

end;

else col3 = column3;

end;

keep col1-col3;

rename col1-col3 = column1-column3;

run;

It's untested. It's ugly. But it should work.

Re: Convert excel data into SAS dataset in transposed format

Reeza — Sun, 11 Sep 2016 01:11:24 GMT

You received a solution to this problem. Did that not work? If not, please explain how so we don't waste time on things that don't work.

Please post sample data as text, preferably a data step, in your post rather than as an attachment.

Re: SAS dataset modification by using variable values

jayakumarmm — Sun, 11 Sep 2016 01:44:00 GMT

Thank you so much. it is working as expected

Re: Convert excel data into SAS dataset in transposed format

jayakumarmm — Sun, 11 Sep 2016 01:46:54 GMT

Issue is resolved in this post "SAS dataset modification by using variable values". Thank you so much.

Re: SAS dataset modification by using variable values

Ksharp — Sun, 11 Sep 2016 02:06:45 GMT


data have;
infile cards truncover;
input (COLUMN1 COLUMN2 COLUMN3) ($);
cards;
TEAM
CLASS
DEPT
CUST1            YES 
AGE                .                    45
MANA2           YES
AGE_1              .                   60
EMPLOY3      NO
AGE_2             .                     90
LOC
GEO
STATE
;
run;
data temp;
 set have;
 if cmiss(column2,column3) ne 2 then do;
  n+1;
  if mod(n,2)=1 then group+1;
  output;
 end;
 drop n;
run;
data want;
 merge temp temp(firstobs=2 keep=group column3 
 rename=(group=_group column3=_column3));
 if group=_group then do;
  column3=_column3;
  output;
 end;
drop _: group;
run;

Re: SAS dataset modification by using variable values

Ksharp — Sun, 11 Sep 2016 02:09:41 GMT


data have;
infile cards truncover;
input (COLUMN1 COLUMN2 COLUMN3) ($);
cards;
TEAM
CLASS
DEPT
CUST1            YES 
AGE                .                    45
MANA2           YES
AGE_1              .                   60
EMPLOY3      NO
AGE_2             .                     90
LOC
GEO
STATE
;
run;
data temp;
 set have;
 if cmiss(column2,column3) ne 2 then do;
  n+1;
  if mod(n,2)=1 then group+1;
  output;
 end;
 drop n;
run;
data want;
 merge temp temp(firstobs=2 keep=group column3 
 rename=(group=_group column3=_column3));
 if group=_group then do;
  column3=_column3;
  output;
 end;
drop _: group;
run;