BookmarkSubscribeRSS Feed
radhikaa4
Calcite | Level 5

I have the following table. What I would like to do is that if there is missing value between min and max day per subject, I would like to insert 0

 

subjectvalueday
151
152
113
125
251
252
244
235

 

 

subjectvalueday
151
152
113
104
125
251
252
203
244
235

 

To be honest, i have no idea how to start. I was thinking to transpose and then assign it of 0 but it is too tedious

4 REPLIES 4
PaigeMiller
Diamond | Level 26

Create all possible combinations of subject and day. Merge this with the original data set. If value is missing, set value to be zero.

 


data have;
    input subject value day;
    cards;
    1 5 1
    1 5 2
    1 1 3
    1 2 5
    2 5 1
    2 5 2
    2 4 4
    2 3 5
;
proc sql;
	create table distinct_day as select distinct day from have;
	create table distinct_subj as select distinct subject from have;
	create table subj_day as select * from distinct_subj,distinct_day;
run;
data want;
    merge have subj_day;
	by subject day;
	if missing(value) then value=0;
run;

This assumes original data is sorted properly by subject and day.

--
Paige Miller
radhikaa4
Calcite | Level 5
This is working, but what if the days are different for each subject? For example, what if first subject had d1, d2, d4 - I would like to input d3 as 0? Not necessarily all day 5 are last


mkeintz
PROC Star

You can use MERGE with a FIRSTOBS option to let you look ahead to find the DAY for the next record:

 

data have;
    input subject value day;
    cards;
    1 5 1
    1 5 2
    1 1 3
    1 2 5
    2 5 1
    2 5 2
    2 4 4
    2 3 5
run;

data want (drop=nxt_:);
  merge have   have (firstobs=2 keep=day rename=(day=nxt_day));
  output;
  if nxt_day>day+1 then do day=day+1 to nxt_day-1;
    value=0;
    output;
  end;
run;

 

This program assumes the data are sorted by subject/day.  And that every subject starts with a DAY value that is not greater than the DAY value ending the prior subject.

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
Ksharp
Super User
data have;
    input subject value day;
    cards;
    1 5 1
    1 5 2
    1 1 3
    1 2 5
    2 5 1
    2 5 2
    2 4 4
    2 3 5
run;

data want(drop=_:);
  merge have 
  have(firstobs=2 keep=subject day rename=(subject=_subject day=_day));
  output;
  if subject=_subject then do;
    do day=day+1 to _day-1;
     value=0;
     output;
	end;
  end;
run;

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

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
  • 4 replies
  • 446 views
  • 0 likes
  • 4 in conversation