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

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  .  .

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
RW9
Diamond | Level 26 RW9
Diamond | Level 26

Well, a slight modification to @data_null__'s solution here:

https://communities.sas.com/t5/Base-SAS-Programming/code-to-pull-forward-missing-values/m-p/345821#M...

 

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;

View solution in original post

5 REPLIES 5
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;
wankata5
Calcite | Level 5

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 . . .
;

 

 

RW9
Diamond | Level 26 RW9
Diamond | Level 26

Well, a slight modification to @data_null__'s solution here:

https://communities.sas.com/t5/Base-SAS-Programming/code-to-pull-forward-missing-values/m-p/345821#M...

 

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;
wankata5
Calcite | Level 5

Thanks for your quick response. 

 

This works fine.

data_null__
Jade | Level 19

@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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

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
  • 5 replies
  • 1000 views
  • 1 like
  • 3 in conversation