Finding Loop solution

[CindyVu]

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;

[bethsmith]

Please can you show a sample of the input data set?

[RW9]

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".

[CindyVu]

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
…						…

[VDD]

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?

[CindyVu]

Oh yes, my bad. I modified it already.