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

Hello!

I have a dataset on which I need to create a "visit" variable (want) with serial numbers according to all available not missing values in another variable (var). The dataset is sorted by ID and Timepoint variables. Below are the 3 variables in the dataset (ID, Timepoint and Var) and last column is what I am looking for (want).

 

IDTimePointVarWant
11..
1251
13..
1472
1543
21..
2291
25..
31..
3281
3332
3453
35..
3654

 

I have tried the following code without success on getting serial number variable (see code and output below):

proc sort data=have;
by ID TimePoint;
run;

data want; set have; want + 1; if Var > 0 then want = 1; else want = .; run;
IDTimePointHaveWant
11..
1251
13..
1471
1541
21..
2291
25..
31..
3281
3331
3451
35..
3651

Thank you for any input regarding this matter!
JJG

 

1 ACCEPTED SOLUTION

Accepted Solutions
JJG
Calcite | Level 5 JJG
Calcite | Level 5

Thank you very much Tom!

View solution in original post

3 REPLIES 3
Tom
Super User Tom
Super User

To have those gaps in the WANT value you need to make another variable.  Otherwise there is no place to remember the previous value.

data have ;
  input ID TimePoint Var Expect;
cards;
1 1 . .
1 2 5 1
1 3 . .
1 4 7 2
1 5 4 3
2 1 . .
2 2 9 1
2 5 . .
3 1 . .
3 2 8 1
3 3 3 2
3 4 5 3
3 5 . .
3 6 5 4
;

data want;
  set have;
  by id ;
  if first.id then counter=0;
  if not missing(var) then do;
     counter + 1;
     want=counter;
  end;
run;

Results

              Time
OBS    ID    Point    Var    Expect    counter    want

  1     1      1       .        .         0         .
  2     1      2       5        1         1         1
  3     1      3       .        .         1         .
  4     1      4       7        2         2         2
  5     1      5       4        3         3         3
  6     2      1       .        .         0         .
  7     2      2       9        1         1         1
  8     2      5       .        .         1         .
  9     3      1       .        .         0         .
 10     3      2       8        1         1         1
 11     3      3       3        2         2         2
 12     3      4       5        3         3         3
 13     3      5       .        .         3         .
 14     3      6       5        4         4         4
JJG
Calcite | Level 5 JJG
Calcite | Level 5

Thank you very much Tom!

FreelanceReinh
Jade | Level 19

Hello @JJG and welcome to the SAS Support Communities!

Glad to see that Tom's solution worked for you. Then it would be fair and help later readers if you marked his helpful reply as the accepted solution, not your own "thank you" post. Could you please change that? It's very easy: Select Tom's post as the solution after clicking "Not the Solution" in the option menu (see icon below) of the current solution.
show_option_menu.png

SAS INNOVATE 2024

innovate-wordmarks-white-horiz.png

SAS is headed back to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team.

Interested in speaking? Content from our attendees is one of the reasons that makes SAS Innovate such a special event!

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.

Get the $99 certification deal.jpg

 

 

Back in the Classroom!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 366 views
  • 0 likes
  • 3 in conversation