BookmarkSubscribeRSS Feed
teja5959
Fluorite | Level 6

subjid  visitnum                                  i want data 

101           1                                           101              1                           

101          1.5                                         101             1.5

101            .                                           101             1.6

101            .                                            101             1.7

101            2                                           101               2

101            .                                            101               2.1

101            .                                            101                2.2

101            3                                            101                3

101            .                                              101               3.1

101            .                                              101               3.2

101            4                                             101                 4

101            .                                              101               4.1

3 REPLIES 3
DanielLangley
Quartz | Level 8
DATA Work.Have;
INPUT subjid visitnum;

CARDS;                             
101 1 
101 1.5
101 .
101 .
101 2
101 .
101 .
101 3
101 .
101 .
101 4
101 .
;
RUN;

DATA WANT;
SET Have;

RETAIN VisitNumWant;

IF visitnum NE . THEN VisitNumWant = visitnum;
ELSE VisitNumWant + 0.1;

DROP visitnum;
RENAME VisitNumWant = VisitNum;
RUN;

Not sure what happens when subjid changes because that is not part of your example. This also assumes that every subjid starts with a non missing visitnum.

mkeintz
PROC Star

I think you should gowith @DanielLangley's solution, but this problem is an excellent vehicle to demonstrate the all variables accessed via a SET (or merge) statement are automatically retained across observations until the next SET/merge statement retrieving the same variables.

 

So the conditional SETs below, retain the variable _VNUM accross multiple incoming obs:

 

DATA Work.Have;
INPUT subjid visitnum;

CARDS;                             
101 1 
101 1.5
101 .
101 .
101 2
101 .
101 .
101 3
101 .
101 .
101 4
101 .
;
RUN;
data want (drop=_:);
  set have;
  if visitnum^=. then set have (keep=visitnum rename=(visitnum=_vnum)) point=_N_;
  else _vnum+.1;
  visitnum=coalesce(visitnum,_vnum);
run;

The COALESCE function tells SAS to retrieve the first non-missing value from a list of numeric values.

 

Edited note: This program assumes that the first obs for each subjid has a  non-missing visitnum (you only show one subjid).  If that assumption is wrong, then the  start of one subjid would be contaminated by the end of the prior subjid. You would need something like this, which resets _VNUM to missing every time a new subjid is encountered:

 

data want (drop=_:);
  set have;
  if subjid^=lag(subjid) then call missing(_vnum);
  if visitnum^=. then set have (keep=visitnum rename=(visitnum=_vnum)) point=_N_;
  else if _vnum^=. then _vnum+.1;
  visitnum=coalesce(visitnum,_vnum);

 

 

 

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

--------------------------
FreelanceReinh
Jade | Level 19

@teja5959: See also this thread from August 2018: assign unscheduled visits, in particular my post regarding numeric representation issues. In your example, the new visit number "1.7" (most likely) requires rounding, otherwise it will not be recognized in an IF or WHERE condition like visitnum=1.7.

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