Hi,
I am relatively new to SAS programming. I have a data set that looks like this:
xxx yyy 1 2 3 4 5 6
a 01 . . z . . .
a 02 . x . . . .
a 02 . . . . c .
b 01 v . . . . .
b 01 . b . . . .
b 01 . . n . . .
c 01 . . . m . .
And my question is how can I make the set to be with a single observation for the variable yyy and to look like this:
xxx yyy 1 2 3 4 5 6
a 01 . . z . . .
a 02 . x . . c .
b 01 v b n . . .
c 01 . . . m . .
Well, a slight modification to @data_null__'s solution here:
Is this:
data sales; input Product $ Brand $ January February March April May June; datalines; TV SAMSUNG . . 1 . . . TV SAMSUNG . . . 2 . . TV SAMSUNG . . . . . 3 TV LG . . 2 . . . TABLET IPAD . 1 . . . . TABLET SAMSUNG . . . 5 . . LAPTOP LENOVO 1 . . . . . LAPTOP DELL . 2 . . . . LAPTOP DELL . . 3 . . . ; run; proc sort data=sales; by product brand; run; data locf; update sales(obs=0) sales; by product brand; if last.brand then output; run;
Please post test data in the form of a datastep.
As such, not typing that out so this is just a guess (as your variable structure is not given and you are mixing character data - z x etc, with numeric missing value . Also variables cannot be called 1 or 2, so I assume _1 or _2), I would retain the variables (but you could also do merge, and possibly even summing up the variables), also what happens when more than one appear?
data want; set have; array orig{6} _1--_6; array new{6} $1; by xxx yyy; retain new:; do i=1 to 6; if orig{i} ne "" then new{i}=orig{i}; end; if last.yy then output; run;
So basically what I have is a data set like this:
data sales;
input Product $ Brand $ January February March April May June;
datalines;
TV SAMSUNG . . 1 . . .
TV SAMSUNG . . . 2 . .
TV SAMSUNG . . . . . 3
TV LG . . 2 . . .
TABLET IPAD . 1 . . . .
TABLET SAMSUNG . . . 5 . .
LAPTOP LENOVO 1 . . . . .
LAPTOP DELL . 2 . . . .
LAPTOP DELL . . 3 . . .
;
And what I am looking for is a data set that looks like this:
data sales2;
input Product $ Brand $ January February March April May June;
datalines;
TV SAMSUNG . . 1 2 . 3
TV LG . . 2 . . .
TABLET IPAD . 1 . . . .
TABLET SAMSUNG . . . 5 . .
LAPTOP LENOVO 1 . . . . .
LAPTOP DELL . 2 3 . . .
;
Well, a slight modification to @data_null__'s solution here:
Is this:
data sales; input Product $ Brand $ January February March April May June; datalines; TV SAMSUNG . . 1 . . . TV SAMSUNG . . . 2 . . TV SAMSUNG . . . . . 3 TV LG . . 2 . . . TABLET IPAD . 1 . . . . TABLET SAMSUNG . . . 5 . . LAPTOP LENOVO 1 . . . . . LAPTOP DELL . 2 . . . . LAPTOP DELL . . 3 . . . ; run; proc sort data=sales; by product brand; run; data locf; update sales(obs=0) sales; by product brand; if last.brand then output; run;
Thanks for your quick response.
This works fine.
@RW9 The default output for the UPDATE statement is LAST unique key, so you can leave off
if last.brand then output;
It doesn't hurt but not needed.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.