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
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.
Did you read this dataset from a text file?
If so, it would be easier to modify the read in process.
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.
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;
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;
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.
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.