BookmarkSubscribeRSS Feed
knveraraju91
Barite | Level 11

Hi I need help in my code.

 

I have a data with 4 variables;

 

Subjid          date                test       period 

1             14OCT2015        Diab    Unscheduled

1             14OCT2015       Sybp    Unscheduled

1             14OCT2015       Diab     Unscheduled

1             14OCT2015        Sybp    Unscheduled

1              21OCT2015        Diab    Unscheduled

1              21OCT2015        Sybp    Unscheduled

1               21OCT2015        diab    Unscheduled

1               21OCT2015        Sybp    Unscheduled

 

 

I need create  a new variable with value=1 for all observation with same date and next value=2 for all observation with same date.

 

I need output like this

 

Subjid          date                test       period                   value

1             14OCT2015        Diab    Unscheduled             1

1             14OCT2015       Sybp    Unscheduled              1

1             14OCT2015       Diab     Unscheduled              1

1             14OCT2015        Sybp    Unscheduled              1

1              21OCT2015        Diab    Unscheduled              2

1              21OCT2015        Sybp    Unscheduled             2

1               21OCT2015        diab    Unscheduled              2

1               21OCT2015        Sybp    Unscheduled             2

 

I am using this code:

 

proc sort data=one out=two
by subjid period date test;
run;


data three;;
set two;
by usubjid vs_dat;
retain value;
if first.vs_dat=1 then value=1;
else value+1;

run;

 

The output:

Subjid          date                test       period                   value

1             14OCT2015        Diab    Unscheduled             1

1             14OCT2015       Sybp    Unscheduled              2

1             14OCT2015       Diab     Unscheduled              3

1             14OCT2015        Sybp    Unscheduled              4

1              21OCT2015        Diab    Unscheduled              1

1              21OCT2015        Sybp    Unscheduled             2

1               21OCT2015        diab    Unscheduled              3

1               21OCT2015        Sybp    Unscheduled             4

 

Please help my code. Thanks

 

 

 

14 REPLIES 14
Reeza
Super User

Why don't your BY values in your data step match your proc sort?

Reeza
Super User

And why have the variable names changed between the two datasets? Date to vs_date? Subjid to usubjid?

 

Your approach is correct, but you need to reference variables appropriately. 

FreelanceReinh
Jade | Level 19

Also, your initial description suggests that DATE is the primary grouping variable (within SUBJID). But your PROC SORT step indicates that PERIOD should have higher priority. Hence, in general, observations with the same DATE might be assigned different group numbers in variable VALUE.

 

If the groups are simply defined by SUBJID and DATE, you could use the following code:

proc sort data=one out=two;
by subjid date;
run;

data three;
set two;
by subjid date;
value+first.date; /* note the implicit RETAIN due to the sum statement */
if first.subjid then value=1; /* EDIT: This resets the value for the first obs. of each SUBJID. */
run;

 

knveraraju91
Barite | Level 11
proc sort data=one out=two;
by subjid date;
run;

data three;
set two;
by subjid date;
value+first.date; /* note the implicit RETAIN due to the sum statement */
run;

Hi this code working but still did not produce expected results. It results in

 

Subjid          date                test       period                   value

1             14OCT2015        Diab    Unscheduled             1

1             14OCT2015       Sybp    Unscheduled              1

1             14OCT2015       Diab     Unscheduled              1

1             14OCT2015        Sybp    Unscheduled              1

1              21OCT2015        Diab    Unscheduled              2

1              21OCT2015        Sybp    Unscheduled             2

1               21OCT2015        diab    Unscheduled              2

1               21OCT2015        Sybp    Unscheduled             2

2             14OCT2015        Diab    Unscheduled               3

2             14OCT2015       Sybp    Unscheduled              3

2             14OCT2015       Diab     Unscheduled              3

2             14OCT2015        Sybp    Unscheduled              3

2              21OCT2015        Diab    Unscheduled              4

2              21OCT2015        Sybp    Unscheduled             4

2               21OCT2015        diab    Unscheduled              4

2               21OCT2015        Sybp    Unscheduled             4

 

The output I needis:

 

1             14OCT2015        Diab    Unscheduled             1

1             14OCT2015       Sybp    Unscheduled              1

1             14OCT2015       Diab     Unscheduled              1

1             14OCT2015        Sybp    Unscheduled              1

1              21OCT2015        Diab    Unscheduled              2

1              21OCT2015        Sybp    Unscheduled             2

1               21OCT2015        diab    Unscheduled              2

1               21OCT2015        Sybp    Unscheduled             2

2             14OCT2015        Diab    Unscheduled             1

2             14OCT2015       Sybp    Unscheduled              1

2             14OCT2015       Diab     Unscheduled              1

2             14OCT2015        Sybp    Unscheduled              1

2              21OCT2015        Diab    Unscheduled              2

2              21OCT2015        Sybp    Unscheduled             2

2               21OCT2015        diab    Unscheduled              2

2               21OCT2015        Sybp    Unscheduled             2

 

Please help; Thanks

Reeza
Super User

Your initial code was correct - only change the BY values.

 

if first.date=1 then value=1;
else value+1;
knveraraju91
Barite | Level 11

Hi I didnot get expected output. Please help. Thanks

FreelanceReinh
Jade | Level 19

@knveraraju91: Sure, you're right! How embarassing! Please accept my apologies and see the correction in my edited post.

knveraraju91
Barite | Level 11
Where is your edited post with answer

##- Please type your reply above this line. Simple formatting, no
attachments. -##
FreelanceReinh
Jade | Level 19

It's in my original post. I've inserted the following line:

if first.subjid then value=1; /* EDIT: This resets the value for the first obs. of each SUBJID. */
knveraraju91
Barite | Level 11

Hi Thanks for your support. But I didnot get my correct answer. I hope you got my question correctly. With following code i am getting the following output. Please look at the value variable values in both, the output i am getting and the output I need. Please help.

 

proc sort data=one out=two;
by subjid date;
run;

data three;
set two;
by subjid date;
value+first.date; /* note the implicit RETAIN due to the sum statement */
run;

Output with the above code:

 

Subjid          date                test       period                   value

1             14OCT2015        Diab    Unscheduled             1

1             14OCT2015       Sybp    Unscheduled              1

1             14OCT2015       Diab     Unscheduled              1

1             14OCT2015        Sybp    Unscheduled              1

1              21OCT2015        Diab    Unscheduled              2

1              21OCT2015        Sybp    Unscheduled             2

1               21OCT2015        diab    Unscheduled              2

1               21OCT2015        Sybp    Unscheduled             2

2             14OCT2015        Diab    Unscheduled               3

2             14OCT2015       Sybp    Unscheduled              3

2             14OCT2015       Diab     Unscheduled              3

2             14OCT2015        Sybp    Unscheduled              3

2              21OCT2015        Diab    Unscheduled              4

2              21OCT2015        Sybp    Unscheduled             4

2               21OCT2015        diab    Unscheduled              4

2               21OCT2015        Sybp    Unscheduled             4

 

The output I need is:

Subjid          date                test       period                   value

 

1             14OCT2015        Diab    Unscheduled             1

1             14OCT2015       Sybp    Unscheduled              1

1             14OCT2015       Diab     Unscheduled              1

1             14OCT2015        Sybp    Unscheduled              1

1              21OCT2015        Diab    Unscheduled              2

1              21OCT2015        Sybp    Unscheduled             2

1               21OCT2015        diab    Unscheduled              2

1               21OCT2015        Sybp    Unscheduled             2

2             14OCT2015        Diab    Unscheduled             1

2             14OCT2015       Sybp    Unscheduled              1

2             14OCT2015       Diab     Unscheduled              1

2             14OCT2015        Sybp    Unscheduled              1

2              21OCT2015        Diab    Unscheduled              2

2              21OCT2015        Sybp    Unscheduled             2

2               21OCT2015        diab    Unscheduled              2

2               21OCT2015        Sybp    Unscheduled             2

FreelanceReinh
Jade | Level 19

You're using the same code as before, not the corrected version.

knveraraju91
Barite | Level 11
Do you mean the following code I need to use;
proc sort data=one out=two;
by subjid date;
run;

data three;
set two;
by subjid date;
if first.date=1 then value=1;
else value+1;
run;




The output I am getting with that code is:

 

Subjid          date                test       period                   value

1             14OCT2015        Diab    Unscheduled             1

1             14OCT2015       Sybp    Unscheduled              2

1             14OCT2015       Diab     Unscheduled              3

1             14OCT2015        Sybp    Unscheduled              4

1              21OCT2015        Diab    Unscheduled              1

1              21OCT2015        Sybp    Unscheduled             2]

1               21OCT2015        diab    Unscheduled              3

1               21OCT2015        Sybp    Unscheduled             4

2             14OCT2015        Diab    Unscheduled               1

2             14OCT2015       Sybp    Unscheduled              2

2             14OCT2015       Diab     Unscheduled              3

2             14OCT2015        Sybp    Unscheduled              4

2              21OCT2015        Diab    Unscheduled              1

2              21OCT2015        Sybp    Unscheduled             2

2               21OCT2015        diab    Unscheduled              3

2               21OCT2015        Sybp    Unscheduled             4

 

But the output I needis:

 

1             14OCT2015        Diab    Unscheduled             1

1             14OCT2015       Sybp    Unscheduled              1

1             14OCT2015       Diab     Unscheduled              1

1             14OCT2015        Sybp    Unscheduled              1

1              21OCT2015        Diab    Unscheduled              2

1              21OCT2015        Sybp    Unscheduled             2

1               21OCT2015        diab    Unscheduled              2

1               21OCT2015        Sybp    Unscheduled             2

2             14OCT2015        Diab    Unscheduled             1

2             14OCT2015       Sybp    Unscheduled              1

2             14OCT2015       Diab     Unscheduled              1

2             14OCT2015        Sybp    Unscheduled              1

2              21OCT2015        Diab    Unscheduled              2

2              21OCT2015        Sybp    Unscheduled             2

2               21OCT2015        diab    Unscheduled              2

2               21OCT2015        Sybp    Unscheduled             2

FreelanceReinh
Jade | Level 19

No, I referred to my own correction, which I copy below for your convenience.

proc sort data=one out=two;
by subjid date;
run;

data three;
set two;
by subjid date;
value+first.date; /* note the implicit RETAIN due to the sum statement */
if first.subjid then value=1; /* EDIT: This resets the value for the first obs. of each SUBJID. */
run;
knveraraju91
Barite | Level 11

Thank you so much that works.

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