BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
sascode
Quartz | Level 8

Hello,

I have a dataset as follows:

data have;
input Id Date :anydtdte. FirstName $ LastName $ x ;
format date monyy6. ;
datalines;
123 Apr-22 A B 20
123 Apr-23 A B 30
123 Apr-24 A B 40
124 Apr-22 E F 10
125 Apr-22 G H 10
125 Apr-23 G H 10

;

 

As i am showing, ID 124 and125 are not present in all dates. How do i add 2 more rows for id 124 and 1 more row for ID 125, and having x just missing?
Thank you.

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User
/*
Sure just need another data step.
*/
data have;
input Id Date :anydtdte. FirstName $ LastName $ x ;
format date monyy6. ;
datalines;
123 Apr-22 A B 20
123 Apr-23 A B 30
123 Apr-24 A B 40
124 Apr-22 E F 10
125 Apr-22 G H 10
125 Apr-23 G H 10
;


proc sql;
create table temp as
select a.*,b.FirstName,b.LastName,b.x
 from 
(select * from (select distinct id from have),(select distinct Date from have)) as a
natural left join have as b
order by id,date;
quit;
data want;
 update temp(obs=0 drop=x) temp(drop=x);
 by id;
 output;
run;
data final_want;
 merge want temp(keep= id date x);
 by id date;
run;

View solution in original post

10 REPLIES 10
Venkatram
Calcite | Level 5
Retian process is id 124and 125 based on condition
sascode
Quartz | Level 8
Yes, because these IDs have not data for all 3 dates
Seadrago
Obsidian | Level 7

So I think you'll want a dataset with all possible combinations of ID and DATE

 

proc sort data=have NODUPKEY out=uniq_id(keep=id); by id; run;

proc sort data=have NODUPKEY out=uniq_date(keep=date); by date; run;

 

proc sql;

  create table all_comb as

  select uniq_id.id, uniq_date.date

  from uniq_id cross join uniq_date;

quit;

 

/*merge all possible combinations with original dataset*/

proc sort data=have; by id date; run;

proc sort data=all_comb; by id date; run;

data want;

  merge all_comb(in=a) have;

  by id date;

  if a;

run;

sascode
Quartz | Level 8
Hi,
first thank you your help,
Your code worked and it successfully retained the ID and date, but it did not retain Firstname and Lastname, and instead, FirstName and LastName are empty.
Thank you.
Kurt_Bremser
Super User

Are your dates meant to be April 1st in different years, or multiple days in April in a given year?

If the first, which century are those years in, if the second, which year?

Kathryn_SAS
SAS Employee

You could use PROC SUMMARY with COMPLETETYPES to get the desired results.

 

data have;
input Id Date :anydtdte. FirstName $ LastName $ x ;
format date monyy6. ;
datalines;
123 Apr-22 A B 20
123 Apr-23 A B 30
123 Apr-24 A B 40
124 Apr-22 E F 10
125 Apr-22 G H 10
125 Apr-23 G H 10
;
run;

proc summary data=have completetypes nway;
class id date;
id firstname lastname;
var x;
output out=want(drop=_type_ _freq_) sum=;
run;

data want;
set want;
if firstname ne ' ' then temp=firstname;
else firstname=temp;
if lastname ne ' ' then temp1=lastname;
else lastname=temp1;
retain temp temp1;
drop temp temp1;
run;

proc print data=want;
run;
Ksharp
Super User
data have;
input Id Date :anydtdte. FirstName $ LastName $ x ;
format date monyy6. ;
datalines;
123 Apr-22 A B 20
123 Apr-23 A B 30
123 Apr-24 A B 40
124 Apr-22 E F 10
125 Apr-22 G H 10
125 Apr-23 G H 10
;


proc sql;
create table temp as
select a.*,b.FirstName,b.LastName,b.x
 from 
(select * from (select distinct id from have),(select distinct Date from have)) as a
natural left join have as b
order by id,date;
quit;
data want;
 update temp(obs=0) temp;
 by id;
 output;
run;
sascode
Quartz | Level 8
Hi, i agree, however, since we know we are adding additional rows for these id's with not completed dates,
can we set x to missing for these addition rows?
Thank you .
Kathryn_SAS
SAS Employee

If you run my code that uses PROC SUMMARY, X is set to missing for the additional rows.

Ksharp
Super User
/*
Sure just need another data step.
*/
data have;
input Id Date :anydtdte. FirstName $ LastName $ x ;
format date monyy6. ;
datalines;
123 Apr-22 A B 20
123 Apr-23 A B 30
123 Apr-24 A B 40
124 Apr-22 E F 10
125 Apr-22 G H 10
125 Apr-23 G H 10
;


proc sql;
create table temp as
select a.*,b.FirstName,b.LastName,b.x
 from 
(select * from (select distinct id from have),(select distinct Date from have)) as a
natural left join have as b
order by id,date;
quit;
data want;
 update temp(obs=0 drop=x) temp(drop=x);
 by id;
 output;
run;
data final_want;
 merge want temp(keep= id date x);
 by id date;
run;

SAS Innovate 2025: Call for Content

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!

Submit your idea!

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
  • 10 replies
  • 1649 views
  • 1 like
  • 6 in conversation