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

Hi.

I have a (company ID-time) panel data. Now I have to calculate standard deviation of variable COGSQ from quarter I/1983 to quarter II/1983, and then quarter I/1983 to quarter III/1983, and so on.....

 

So basically, for company with GVKEY 001001;

t=0 is quarter I/1983

at t=1 (QII/1983), I need to find standard deviation of X from t=0 to t=1

at t=2 (QIII/1983), std of X from t=0 to t=2

and so on,

 

Do you have any idea how to do this?

So the dataset is like this

gvkeydatadateCompanyCOGSQSTD
100119830331A1.258NA
100119830630A1.4std from QI/1983 to QII/1983
100119830930A1.5std from QI/1983 to QIII/1983
100119831231A1.6 
……..………….……. 
100119860331A2.3 
100219840430B3.5 
100219840731B6.5 
…..………..….. 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Peter_C
Rhodochrosite | Level 12

my code with line breaks to make it easier to read

data progressive_stddevs ;
do item= 1 by 1 until( last.company ) ;
   set your_data ;
   by company ;
   array cogX(1200) ; * 1200 months, probably enough per company? ;
   cogX(item) = cogsq ;
   n_cogs = n( of cogX(*) ) ;
   if n_cogs > 1 then COGSTD = std( of cogX(*) ) ;
   output ;
end ;
drop cogX: ; 
run ;

 the array is filled progressively until end of the company, when it will be emptied by restarting the data step iteration

The STD() function ignores empty cells in the array 

View solution in original post

16 REPLIES 16
PeterClemmensen
Tourmaline | Level 20

Do you have SAS/ETS? If so check out PROC EXPAND 🙂

trungcva112
Obsidian | Level 7

Hi, I just check the PROC EXPAND but it is for transform/interpolate variables, so I dont see how this related to my case. Could you explain more for me? Please, I'm a newbie

 

Please note that this is different from rolling window standard deviation. Rolling window std has a fixed number of observations (fixed window range) but in this situation, the window range is larger when time progress

PeterClemmensen
Tourmaline | Level 20

PROC EXPAND is indeed for transforming/interpolating variables and you want to transform a variable 🙂

 

Take a look at the Transformation Operators site at the documentation. You want to create a moving stadard deviation, so the MOVSTD operator might be worth taking a look at. 

 

 

 

 

PeterClemmensen
Tourmaline | Level 20

Post some sample of your data (not in the form of a picture) if you want some code answer 🙂

trungcva112
Obsidian | Level 7
Hi. I just post a sample of my data. Could you help me please?
PeterClemmensen
Tourmaline | Level 20

Ah, I understand. You can still use PROC EXPAND, but this may be simpler to do in a data step like this:

 

data have; 
input gvkey$ datadate:yymmdd10. Company$ COGSQ;
quarter=qtr(datadate);
year=year(datadate);
format datadate yymmdd10.;
datalines;
1001 19830331 A 1.258
1001 19830630 A 1.4 
1001 19830930 A 1.5
1001 19831231 A 1.6
1001 19860331 A 2.3
1002 19840430 B 3.5
1002 19840731 B 6.5
;

proc sort data=have;
	by gvkey year quarter;
run;

data want;
	set have;
	by gvkey;
	if first.gvkey then COGSQ_first=COGSQ;

	if not first.gvkey then std_dev = std(COGSQ_first, COGSQ);
	
	retain COGSQ_first;
	drop COGSQ_first;
run;
trungcva112
Obsidian | Level 7
Thank you, but I am confused a little bit about the line:
if not first.gvkey then std_dev = std(COGSQ_first, COGSQ);

So assume that it is on quarter III/1983, so this line will give me std_dev of COGSQ for 3 observations: Q1, Q2, Q3/1983 or just std_dev of 2 observations:Q1 and Q3/1983?

Peter_C
Rhodochrosite | Level 12

if this is only qtr/monthly intervals, it might be a good case to demonstrate array handling. SAS/IML would also simplify the resultant solution. What follows uses only a data step and assumes the monthly data arrive in month order within each company

data progressive_stddevs ;
do item= 1 by 1 until( last.company ) ;
set your_data ;
by company ;
array cogX(1200) ; * 1200 months, probably enough per company? ;
cogX(item) = cogsq ;
n_cogs = n( of cogX(*) ) ;
if n_cogs > 1 then COGSTD = std( of cogX(*) ) ;
output ;
end ;
drop cogX: ;
run ;

 the array is filled progressively until end of the company, when it will be emptied by restarting the data step iteration

The STD() function ignores empty cells in the array 

Peter_C
Rhodochrosite | Level 12
sorry I posted code from SAS Studio (so line-breaks are missing)
Shortly I'll post the code with line breaks
Peter_C
Rhodochrosite | Level 12

my code with line breaks to make it easier to read

data progressive_stddevs ;
do item= 1 by 1 until( last.company ) ;
   set your_data ;
   by company ;
   array cogX(1200) ; * 1200 months, probably enough per company? ;
   cogX(item) = cogsq ;
   n_cogs = n( of cogX(*) ) ;
   if n_cogs > 1 then COGSTD = std( of cogX(*) ) ;
   output ;
end ;
drop cogX: ; 
run ;

 the array is filled progressively until end of the company, when it will be emptied by restarting the data step iteration

The STD() function ignores empty cells in the array 

trungcva112
Obsidian | Level 7
Thanks. I will try that. But each company has different number of observations. For example, company 1 has data from Q1/1983 to Q1/1986 (13 observations), company 2 has data Q1/1984 to QI/2000 (64 observations). So can I still use array cogX(1200)?
mkeintz
PROC Star

You want the std of within-company quarterly changes in cost-of-goods-sold (COGSQ), in which each quarter may have a slightly different population of companies (with id variable GVKEY).

 

Your data are sorted by GVKEY/DATADATE.  So if you calculate every quarterly change (use the DIF function, where DIF(x)=X-lag(X)), then just keep every record but the first record for each GVKEY.  Also make sure datadate and lag(datadate) are exactly 3 months apart (to avoid missed quarters or misalignment due to companies changing fiscal year definitions). 

 

Once you've made this data set (call it NEED), then run proc means on it, with class datadate.  Using the ODS output statement, and the STACKODSOUTPUT option puts data set file WANT in a nice format:

 

data need /view=need;
  set have;
  by gvkey datadate;
  cogsq_change=dif(cogsq);
  months_elapsed = intck('month',lag(datadate),datadate);

  if first.gvkey=0 and months_elapsed=3;
run;

ods output summary=want;
ods listing close;
proc means data=need stackodsoutput;
  class datadate;
  var cogsq_change;
run;
ods output close;
ods listing;

 

 

This will produce 5 variables for each datadate, one each for N, MEAN, STD, MIN, and MAX value of cogsq_change.  The datadate identified the last date of the quarterly change.  If you want more statistics, then make a list of wanted stats on the proc means statement.

 

The ODS LISTING CLOSE, and ODS LISTING avoids the (presumably unwanted) printing of all those stats. Instead the ods output summary=want; statement tells sas to put those results into a data set file named want.

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
Peter_C
Rhodochrosite | Level 12
Reply to @trungcva112
The array can be a fixed size. That needs to be large enough for the company with the most history. I thought 1200, to allow for 100 years of monthly data. If there is less history it is not a problem.
Like all SAS statistical functions only non-missing values will be used.
At the start of each data step iteration the whole array is set missing. Each value is inserted into the array just after each set statement and a STD() calculated. Each data step iteration ends when all rows of a company have been read.
Peter_C
Rhodochrosite | Level 12
There might be a problem if a company has tlmore than one period of history. That data step would treat all data for a company as one history

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