BookmarkSubscribeRSS Feed
hikhieu
Calcite | Level 5

Dear SAS community users,

 

I have an unbalanced panel data set with firm years. In that set, I have a dummy marking an event year (year when new stock is issued). I want to find the median of a variable (sale, for example) 3 years before the event year for each firm ID. Then I want to find the median of the same variable 5 years after the event year for each firm ID.

 

I'd appreciate any help here. Thank you in advance.

 

Regards,

Hinh

5 REPLIES 5
Reeza
Super User
Create a flag that identifies before and after.
Run a proc means with the flag as a grouping variable, either BY or CLASS.

Need more help? Please post more details, including sample data and expected output.
PGStats
Opal | Level 21

Do it in one step with correlated sub-queries:

 


proc sql;
create table medianSales as
select 
    firm,
    (   select median(sales) 
        from have 
        where firm=a.firm and year between a.year-3 and a.year-1 )
        as medianSalesBefore,
    (   select median(sales) 
        from have 
        where firm=a.firm and year between a.year+1 and a.year+5 )
        as medianSalesAfter
from have as a
where flag;
select * from medianSales;
quit;
PG
Reeza
Super User

If you have SAS 9.4 - median function not valid in PROC SQL in earlier versions.

 

It won't generate an error, but it won't be the metric you expect. 

PGStats
Opal | Level 21

Thanks @Reeza. A good thing to know.

PG
hikhieu
Calcite | Level 5
Thanks so much for your prompt help, PG and Reeza. I will try and let you know.

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
  • 597 views
  • 1 like
  • 3 in conversation