BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Ardaneth
Fluorite | Level 6

Dear SAS-Community,

 

i have a question for you.

 

I have data in the structure you can see on the left side. I want it to be summarized by the ID, like you can see on the right side.

 

ID   COUNT          ID   COUNT_sum

1        3       ->      1         3

1        1       ->      1         4

1       12      ->      1        16

1        0       ->      1        16

1        2       ->      1        18

2        0       ->      2         0

2        4       ->      2         4

2        6       ->      2        10

3        2       ->      3         2

3        1       ->      3         3

 

Sadly the lag function doesnt work like id like it to, otherwise this should have been the solution:

 

data count2; set count;

    if ID ne lag1 (ID) then Count_sum=count;

run;

 

data count3; set count2;

    if ID = lag1 (ID) then Count_sum=lag1(Count_sum)+count;

run;

 

Like i said the lag function doesnt seem to work in an "if then" statement as i want it to, but it should get you an impression of what i want to do with my data. The dataset is pretty big and Spells for one ID can go up to 170. I would be glad about any suggestions how to handle this problem.

 

Best Wishes!

1 ACCEPTED SOLUTION

Accepted Solutions
PaigeMiller
Diamond | Level 26
data count2;
    set count;
    by id;
    if first.id then count_sum=0;
    count_sum+count;
run;
--
Paige Miller

View solution in original post

4 REPLIES 4
PaigeMiller
Diamond | Level 26
data count2;
    set count;
    by id;
    if first.id then count_sum=0;
    count_sum+count;
run;
--
Paige Miller
Ardaneth
Fluorite | Level 6

Neat!

 

This is much more simple/elegant than i hoped for.

 

Thanks Mr./Mrs. Miller!

gfarkas
Calcite | Level 5

Don't you need a RETAIN statement there?

Reeza
Super User
Just a note that lag doesn't work in if/then statements. You can use IFN/IFC or you can calculate lag and then apply your IF/THEN condition.

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 connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 4 replies
  • 1155 views
  • 3 likes
  • 4 in conversation