SAS dataset modification by using variable values

[jayakumarmm]

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

[Reeza]

Did you read this dataset from a text file?

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

[Astounding · Accepted solution]

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]

Thank you so much. it is working as expected

[Ksharp]

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]

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]

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]

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]

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