BookmarkSubscribeRSS Feed
Ronein
Meteorite | Level 14

Hello

In the following data set there are 6 rows for each ID.

I need to create a data set that contain 1 row for each customer and have information of #months that Y was lower than 3000.

Expected results:

for ID=1 we will get 0

for ID=1 we will get 1

for ID=1 we will get 4

What is the way to do it please?

 

Data tbl;
input ID Y;
cards;
1 3000
1 3400
1 3100
1 3200
1 3300
1 3300
2 3500
2 2900
2 3100
2 3100
2 3200
2 3300
3 3200
3 2900
3 2800
3 2800
3 2950
3 3200
;
run;
5 REPLIES 5
PeterClemmensen
Tourmaline | Level 20
proc sql;
   create table want as
   select ID, sum(Y<3000) as Ylower3000
   from tbl
   group by ID;
quit;

 

Result:

 

 

ID  Ylower3000 
1   0 
2   1 
3   4 

 

Ronein
Meteorite | Level 14

What is the data set way to do it?

I know to do it via proc sql  but don't know via data set way

PeterClemmensen
Tourmaline | Level 20

You can do it with a data step like this

 

data want(drop=Y); 
	do until (last.id);
		set tbl;
		by id;
		if Y < 3000 then Ylower3000+1;
	end;
    output;
    Ylower3000=0;
run;
PeterClemmensen
Tourmaline | Level 20

Of course, this approach relies on the input data to be sorted by ID. If it is not, then you can do something like this

 

Data tbl;
input ID Y;
cards;
1 3000
1 3200
1 3300
1 3300
3 2800
3 2800
3 2950
3 3200
2 3500
2 2900
2 3100
2 3100
2 3200
1 3400
1 3100
2 3300
3 3200
3 2900
;
run;

data _null_;
    if _N_=1 then do;
        declare hash h(ordered:'Y');
        h.definekey('ID');
        h.definedata('ID', 'Ylower3000');
        h.definedone();
    end;

    set tbl end=lr;

    if h.check() ne 0 then do;
        Ylower3000=0;
        h.add();
    end;
    if Y<3000 then do;
        rc=h.find();
        Ylower3000+1;
        rc=h.replace();
    end;
    
    if lr then h.output(dataset:'want');
run;
yabwon
Onyx | Level 15

Hi,

 

Just for fun. I think that direct addressing also will do in this case 🙂

 

Bart

Data tbl;
input ID Y;
cards;
1 3000
1 3200
1 3300
1 3300
3 2800
3 2800
3 2950
3 3200
2 3500
2 2900
2 3100
2 3100
2 3200
1 3400
1 3100
2 3300
3 3200
3 2900
5 3200
5 2900
;
run;


%let size = 10000;
data want;
    array t[&size.] _temporary_; 

    min = &size.; max = 0;
    do until(eof);
      set tbl end=eof;
      t[id] + (Y<3000);
      min = min><id;
      max = max<>id;
    end;

    do id = min to max;
      Ylower3000 = t[id];
      put _all_;
      if Ylower3000 > .z then output;
    end;
  stop;
  keep id Ylower3000; 
run;

 

_______________
Polish SAS Users Group: www.polsug.com and communities.sas.com/polsug

"SAS Packages: the way to share" at SGF2020 Proceedings (the latest version), GitHub Repository, and YouTube Video.
Hands-on-Workshop: "Share your code with SAS Packages"
"My First SAS Package: A How-To" at SGF2021 Proceedings

SAS Ballot Ideas: one: SPF in SAS, two, and three
SAS Documentation



Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
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
  • 523 views
  • 1 like
  • 3 in conversation