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

Hello community,

 

I'm trying to utilize PROC SQL to count the frequency of a variable and then use the count from a different row to create a new variable.

 

An example of my data:

 

Worker IDFacilityYear
1A

2010

2A2010
3A2011
4A2012
5A2012
6B2011
7B2011
8B2012
9B2012
10B2013

 

What I want to accomplish in PROC SQL:

 

FacilityYearCountCount from Previous Year
A201020
A201112
A201221
A201302
B201000
B201120
B201222
B201312

I know how to get to the "Count" variable using Group by, however getting the "Count from Previous Year" is where I'm having difficulty.

 

And then ultimately I want to use the "Count from previous year" variable and add it on to the original table by facility and year, so:

 

Worker IDFacilityYearCount from Previous Year
1A

2010

0

2A20100
3A20112
4A20121
5A20121
6B20110
7B20110
8B20122
9B20122
10B20132

 

I'm open to any and all suggestions! Thank you all in advance.

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

One way to create the table with both year's counts, assuming you did the summary to get the count into a data set named CountSummary is to join that result to itself using the Year with an offset. The Coalesce function returns the first non-missing value as a result. So when you have a 'previous year' without a count the b.count would be missing and should get 0 as the result.

 

proc sql;
   create table want as
   select a.*
          ,coalesce(b.count,0) as CountPrevYear
   from countsummary as a
        left join
        countsummary as b
        on a.facility=b.facility
           and a.year= (b.year+1)
   ;
quit;

But you don't need the intermediate table. You could join the CountSummary to the other data using the same year offset approach.

View solution in original post

2 REPLIES 2
ballardw
Super User

One way to create the table with both year's counts, assuming you did the summary to get the count into a data set named CountSummary is to join that result to itself using the Year with an offset. The Coalesce function returns the first non-missing value as a result. So when you have a 'previous year' without a count the b.count would be missing and should get 0 as the result.

 

proc sql;
   create table want as
   select a.*
          ,coalesce(b.count,0) as CountPrevYear
   from countsummary as a
        left join
        countsummary as b
        on a.facility=b.facility
           and a.year= (b.year+1)
   ;
quit;

But you don't need the intermediate table. You could join the CountSummary to the other data using the same year offset approach.

Ksharp
Super User
data have;
infile cards expandtabs;
input WorkerID Facility $ Year;
cards;
1 A 2010
2 A 2010
3 A 2011
4 A 2012
5 A 2012
6 B 2011
7 B 2011
8 B 2012
9 B 2012
10 B 2013
;

proc sql;
create table want as
select *,
 (select count(*) from have 
where Facility=a.Facility and Year=a.Year-1) as CountfromPreviousYear
 from have as a ;
quit;

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 2 replies
  • 2685 views
  • 2 likes
  • 3 in conversation