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

Hi!

 

I have the following dataset:

ID      spellnr      duration

101        1                1

101        1                2

101        1                3

101        2                1

101        2                2

101        3                1

101        3                2

101        3                3

102        1                1

102        1                2

102        2                1

 

I want the following dataset:

ID      spellnr      duration      maxspellnr

101        1                1                   3

101        1                2                   3

101        1                3                   3

101        2                1                   3

101        2                2                   3

101        3                1                   3

101        3                2                   3

101        3                3                   3

102        1                1                   2

102        1                2                   2

102        2                1                   2

 

That is, I want to know what the maximum number of spells an individual is subject to.

 

Thank you for your time!

 

1 ACCEPTED SOLUTION

Accepted Solutions
Cynthia_sas
SAS Super FREQ

Hi:

   As an alternative, here's an example that does not use SQL, but instead counts on the fact that if you sorted by ID and descending SPELLNR, then the max of SPELLNR would be on the first row for ID. Then all you need is a RETAIN to retain the max value for all the rows for the same ID.

 

cynthia

 

use_descending.png

View solution in original post

5 REPLIES 5
Shmuel
Garnet | Level 18

You need 2 steps:

 

1) compute the max spellnr per ID:

    proc sql;

            create table tmp as select ID, max(spellnr) as maxspellnr 

             from  have group by ID;

    quit;

2) join the max value to the original data:

    proc sql;

            create table want as

            select a.* , b.maxspellnr

            from have as a

            left join tmp as b

            on a.ID = b.ID;

    quit;

          

 

 

Cynthia_sas
SAS Super FREQ

Hi:

   As an alternative, here's an example that does not use SQL, but instead counts on the fact that if you sorted by ID and descending SPELLNR, then the max of SPELLNR would be on the first row for ID. Then all you need is a RETAIN to retain the max value for all the rows for the same ID.

 

cynthia

 

use_descending.png

MikeZdeb
Rhodochrosite | Level 12

Hi. You can use PROC SQL and get your new data set in one step ....

 

proc sql;
create table new as
select *, max(spellnr) as maxspellnr from x
group id;
quit;

NielsKraaer
Fluorite | Level 6

I went with Cynthias solution, but I am annoyed at how hard it is to find the maximum number in a column. Thank you for your posts!

MikeZdeb
Rhodochrosite | Level 12

Hi, is this much code really that annoying ...

 

proc sql;
create table new as
select *, max(spellnr) as maxspellnr from x
group id;
quit;

 

Finding the maximum number requires little SAS code (PROC MEANS or SUMMARY will do that). It's the remerging with the original observations that requires the extra programing. PROC SQL finds the maximum value within groups and also does that remerging with the least amnount of SAS code.

 

Here are a couple other ideas ...

 

* read the data set twice ... add maximum value in the second pass through the data;

* almost as short as the PROC SQL solution shown above;

data xx;

do until (last.id);
set x (in=pass1) x;
  by id;
  if pass1 then maxspellnr = max(of maxspellnr, spellnr);
  else output;
end;

run;

 

or ...

 

* use PROC SUMMARY to find the maximum value;

proc summary data=x nway;
var spellnr;
class id;
output max=maxspellnr out=maxsp(index=(id) keep=id maxspellnr);
run;

 

* add the maximum value to the data set;

data xx;
set x;
set maxsp key=id/unique;
run;

 

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
  • 5 replies
  • 4326 views
  • 2 likes
  • 4 in conversation