Hi, I have codes like below and I have to do it twenty times.I wonder whether there is a loop solution to make it faster.
Let me explain the structure of my data:
the file name "revtota_0305" contains name of variable and time period. I have 4 different variables (revtota, inttota, sgatota, cogstota) and 5 time period (0305, 0608, 0911, 1214, 1517)
proc sort data=mal2.revtota_0305 nodupkey out=mal2.revtota_0305;
by company CUSIP SIC ISIN SEDOL;
run;
proc transpose data=mal2.revtota_0305 out=mal2.revtota_0305;
var
_2003 _2004 _2005;
by company CUSIP SIC ISIN SEDOL;
run;
data mal2.revtota_0305;
set mal2.revtota_0305;
date=substr(left(_NAME_),2,4);
Year=date*1;
rename COL1=revtota;
drop _NAME_ date;
run;
PROC SQL;
CREATE TABLE mal2.revtota_0305_trans AS
SELECT Company,
CUSIP,
SIC,
ISIN,
SEDOL,
Year,
revtota
FROM mal2.revtota_0305 t1;
QUIT;
Please can you show a sample of the input data set?
No test data in the form of a datastep provided.
No required output provided.
At a guess, normalise your data, so there is one row per year rather than having the three columns _2003 _2004 etc. This is always advisable anyways. Then once you have data in a long format, its simply of a matter of doing processing "by group".
Sorry for missing information. The data file sample is attached below
I want to create table with format like this:
Company | CUSIP | SIC | ISIN | SEDOL | COGStoTA | Year |
A | 2003 | |||||
A | 2004 | |||||
A | 2005 | |||||
B | 2003 | |||||
… | … |
you read and write to the same SAS dataset "data=mal2.revtota_0305 nodupkey out=mal2.revtota_0305" at the beginning of the process,
but in the SQL step your in dataset FROM mydata.revtota t1;is not the same name?
Oh yes, my bad. I modified it already.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.