DATA Step, Macro, Functions and more

SAS dataset modification by using variable values

Accepted Solution Solved
Reply
Contributor
Posts: 55
Accepted Solution

SAS dataset modification by using variable values

[ Edited ]

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

 


Accepted Solutions
Solution
‎09-10-2016 09:43 PM
Super User
Posts: 5,093

Re: SAS dataset modification by using variable values

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.

View solution in original post


All Replies
Super User
Posts: 17,912

Re: SAS dataset modification by using variable values

Did you read this dataset from a text file?

 

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

Solution
‎09-10-2016 09:43 PM
Super User
Posts: 5,093

Re: SAS dataset modification by using variable values

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.

Contributor
Posts: 55

Re: SAS dataset modification by using variable values

Thank you so much. it is working as expected
Super User
Posts: 9,687

Re: SAS dataset modification by using variable values


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;

Super User
Posts: 9,687

Re: SAS dataset modification by using variable values


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;

Contributor
Posts: 55

Convert excel data into SAS dataset in transposed format

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. 

 

 

Super User
Posts: 17,912

Re: Convert excel data into SAS dataset in transposed format

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. 

Contributor
Posts: 55

Re: Convert excel data into SAS dataset in transposed format

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

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 716 views
  • 0 likes
  • 4 in conversation