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.
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.