BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
jayakumarmm
Quartz | Level 8

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

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

8 REPLIES 8
Reeza
Super User

Did you read this dataset from a text file?

 

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

Astounding
PROC Star

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.

jayakumarmm
Quartz | Level 8
Thank you so much. it is working as expected
Ksharp
Super User

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;

Ksharp
Super User

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;

jayakumarmm
Quartz | Level 8

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. 

 

 

Reeza
Super User

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. 

jayakumarmm
Quartz | Level 8
Issue is resolved in this post "SAS dataset modification by using variable values". Thank you so much.

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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