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

Hello all,

I have the code below that needs to be applied in all stocks of the data. So, if i have 800 stocks, I should run the code 800 times, each time changing the stock name (red highlighted). How can I make SAS automatically run the code for each stock and then save it as a file with the name of each stock?

Many thanks in advance  

proc sql;
create table C as
select*
from work.data
where stock = "ACERALIA";
quit;

proc sql;
create table b as
select date,code,value
from c;
quit;

proc sql;
create table f as
select a.*,b.*
from work.Date as a left join work.b as b
on a.date=b.date;
quit;

proc sort data=work.f nodupkey;by code date;run;
data a; set f; format date monyy7.; run;
proc sql; create table a_ as select distinct date,0.1 as code from a; quit;

data a2; set a a_;run;
proc sort data= a2; by code date;run;
proc transpose data=a2 out=a3(where=(code^=0.1));
by code;
id date;
var value;
run;
data work.a4;
set work.a3;
drop _NAME_;
run;
Data old;
Code=0 Mar1995=1 jun1995=2 sep1995=3 dec1995=4 mar1996=5 jun1996=6 sep1996=7 dec1996=8 mar1997=9 jun1997=10 sep1997=11 dec1997=12 mar1998=13 jun1998=14 sep1998=15 dec1998=16 mar1999=17 jun1999=18 sep1999=19 dec1999=20 mar2000=21 jun2000=22 sep2000=23 dec2000=24 mar2001=25 jun2001=26 sep2001=27 dec2001=28 mar2002=29 jun2002=30 sep2002=31 dec2002=32 mar2003=33 jun2003=34 sep2003=35 dec2003=36 mar2004=37 jun2004=38 sep2004=39 dec2004=40 mar2005=41 jun2005=42 sep2005=43 dec2005=44 mar2006=45 jun2006=46 sep2006=47 dec2006=48 mar2007=49 jun2007=50 sep2007=51 dec2007=52 mar2008=53 jun2008=54 sep2008=55 dec2008=56 mar2009=57 jun2009=58 sep2009=59 dec2009=60 mar2010=61 jun2010=62 sep2010=63 64;run;

Data new;
retain code Mar1995 jun1995 sep1995 dec1995 mar1996 jun1996 sep1996 dec1996 mar1997 jun1997 sep1997 dec1997 mar1998 jun1998 sep1998 dec1998 mar1999 jun1999 sep1999 dec1999 mar2000 jun2000 sep2000 dec2000 mar2001 jun2001 sep2001 dec2001 mar2002 jun2002 sep2002 dec2002 mar2003 jun2003 sep2003 dec2003 mar2004 jun2004 sep2004 dec2004 mar2005 jun2005 sep2005 dec2005 mar2006 jun2006 sep2006 dec2006 mar2007 jun2007 sep2007 dec2007 mar2008 jun2008 sep2008 dec2008 mar2009 jun2009 sep2009 dec2009 mar2010 jun2010 sep2010;set a4;run;


proc sql;
create table a5 as
select a.*,b.*
from work.funds as a left join work.a4 as b
on a.code=b.code;
quit;

data asigns;
set WORK.a5;
array vol(63) mar1995 --sep2010;
array si (62) $  si1-si62;
do i= 2 to 63;
if vol(i) > vol(i-1) then si(i-1)='+';
else if vol(i) <  vol(i-1) then si(i-1)='-';
else si(i-1)='&';
end;
RUN;


1 ACCEPTED SOLUTION

Accepted Solutions
Linlin
Lapis Lazuli | Level 10

Hi,

I tried the sample code and it works:

title;

data stocks;

input st $;

cards;

ibm

ge

co

c

jmp

bp

;

/* put all your stocks in macro variable allstock */

proc sql noprint;

  select st into :allstock separated by ' '

    from stocks;

quit;

%macro test();

%do i=1 %to %sysfunc(countw(&allstock));

%let st=%scan(&allstock,&i);

proc print data=stocks;

   where st="&st";

   title from stock "&st";

   run;

  %end;

%mend;

%test

/***************************************************/

for your case please try the code below:

data stocks;

input st $;

cards;

ibm

ge

co

c

jmp

bp

;

/* put all your stocks in macro variable allstock */

proc sql noprint;

  select st into :allstock separated by ' '

    from stocks;

quit;

%macro test();

%do i=1 %to %sysfunc(countw(&allstock));

%let st=%scan(&allstock,&i);

proc sql;

create table C as

select*

from work.data

where stock = "&st";

quit;

proc sql;

create table b as

select date,code,value

from c;

quit;

proc sql;

create table f as

select a.*,b.*

from work.Date as a left join work.b as b

on a.date=b.date;

quit;

proc sort data=work.f nodupkey;by code date;run;

data a; set f; format date monyy7.; run;

proc sql; create table a_ as select distinct date,0.1 as code from a; quit;

data a2; set a a_;run;

proc sort data= a2; by code date;run;

proc transpose data=a2 out=a3(where=(code^=0.1));

by code;

id date;

var value;

run;

data work.a4;

set work.a3;

drop _NAME_;

run;

Data old;

Code=0 Mar1995=1 jun1995=2 sep1995=3 dec1995=4 mar1996=5 jun1996=6 sep1996=7 dec1996=8 mar1997=9 jun1997=10 sep1997=11 dec1997=12 mar1998=13 jun1998=14 sep1998=15 dec1998=16 mar1999=17 jun1999=18 sep1999=19 dec1999=20 mar2000=21 jun2000=22 sep2000=23 dec2000=24 mar2001=25 jun2001=26 sep2001=27 dec2001=28 mar2002=29 jun2002=30 sep2002=31 dec2002=32 mar2003=33 jun2003=34 sep2003=35 dec2003=36 mar2004=37 jun2004=38 sep2004=39 dec2004=40 mar2005=41 jun2005=42 sep2005=43 dec2005=44 mar2006=45 jun2006=46 sep2006=47 dec2006=48 mar2007=49 jun2007=50 sep2007=51 dec2007=52 mar2008=53 jun2008=54 sep2008=55 dec2008=56 mar2009=57 jun2009=58 sep2009=59 dec2009=60 mar2010=61 jun2010=62 sep2010=63 64;run;

Data new;

retain code Mar1995 jun1995 sep1995 dec1995 mar1996 jun1996 sep1996 dec1996 mar1997 jun1997 sep1997 dec1997 mar1998 jun1998 sep1998 dec1998 mar1999 jun1999 sep1999 dec1999 mar2000 jun2000 sep2000 dec2000 mar2001 jun2001 sep2001 dec2001 mar2002 jun2002 sep2002 dec2002 mar2003 jun2003 sep2003 dec2003 mar2004 jun2004 sep2004 dec2004 mar2005 jun2005 sep2005 dec2005 mar2006 jun2006 sep2006 dec2006 mar2007 jun2007 sep2007 dec2007 mar2008 jun2008 sep2008 dec2008 mar2009 jun2009 sep2009 dec2009 mar2010 jun2010 sep2010;set a4;run;

proc sql;

create table a5 as

select a.*,b.*

from work.funds as a left join work.a4 as b

on a.code=b.code;

quit;

data &st;

set WORK.a5;

array vol(63) mar1995 --sep2010;

array si (62) $  si1-si62;

do i= 2 to 63;

if vol(i) > vol(i-1) then si(i-1)='+';

else if vol(i) <  vol(i-1) then si(i-1)='-';

else si(i-1)='&';

end;

RUN;

  %end;

%mend;

%test

Linlin

View solution in original post

10 REPLIES 10
art297
Opal | Level 21

Before anyone tries to show you what you've asked, it would be helpful if you posted sample data for the files involved and provided an explanation of what you are trying to accomplish.

The code you provided appears to do some things that appear odd.  First you create a table with just a certain value of stock.  Then you create another table, based on the table you created, but only containing 3 variables.  Then you join those two files.  Why?

Then you do a sort to get rid of duplicates.  That would probably be more easily accomplished using a distinct clause in your original proc sql.

I didn't try to follow the rest of the code because I didn't understand what you were trying to accomplish with the first parts.

Linlin
Lapis Lazuli | Level 10

is "asigns" your final output dataset?  and you want to replace "asigns" with stock name?  if so you can try the code below:

%macro test(st);

proc sql;

create table C as

select*

from work.data

where stock = "&st";

quit;

proc sql;

create table b as

select date,code,value

from c;

quit;

proc sql;

create table f as

select a.*,b.*

from work.Date as a left join work.b as b

on a.date=b.date;

quit;

proc sort data=work.f nodupkey;by code date;run;

data a; set f; format date monyy7.; run;

proc sql; create table a_ as select distinct date,0.1 as code from a; quit;

data a2; set a a_;run;

proc sort data= a2; by code date;run;

proc transpose data=a2 out=a3(where=(code^=0.1));

by code;

id date;

var value;

run;

data work.a4;

set work.a3;

drop _NAME_;

run;

Data old;

Code=0 Mar1995=1 jun1995=2 sep1995=3 dec1995=4 mar1996=5 jun1996=6 sep1996=7 dec1996=8 mar1997=9 jun1997=10 sep1997=11 dec1997=12 mar1998=13 jun1998=14 sep1998=15 dec1998=16 mar1999=17 jun1999=18 sep1999=19 dec1999=20 mar2000=21 jun2000=22 sep2000=23 dec2000=24 mar2001=25 jun2001=26 sep2001=27 dec2001=28 mar2002=29 jun2002=30 sep2002=31 dec2002=32 mar2003=33 jun2003=34 sep2003=35 dec2003=36 mar2004=37 jun2004=38 sep2004=39 dec2004=40 mar2005=41 jun2005=42 sep2005=43 dec2005=44 mar2006=45 jun2006=46 sep2006=47 dec2006=48 mar2007=49 jun2007=50 sep2007=51 dec2007=52 mar2008=53 jun2008=54 sep2008=55 dec2008=56 mar2009=57 jun2009=58 sep2009=59 dec2009=60 mar2010=61 jun2010=62 sep2010=63 64;run;

Data new;

retain code Mar1995 jun1995 sep1995 dec1995 mar1996 jun1996 sep1996 dec1996 mar1997 jun1997 sep1997 dec1997 mar1998 jun1998 sep1998 dec1998 mar1999 jun1999 sep1999 dec1999 mar2000 jun2000 sep2000 dec2000 mar2001 jun2001 sep2001 dec2001 mar2002 jun2002 sep2002 dec2002 mar2003 jun2003 sep2003 dec2003 mar2004 jun2004 sep2004 dec2004 mar2005 jun2005 sep2005 dec2005 mar2006 jun2006 sep2006 dec2006 mar2007 jun2007 sep2007 dec2007 mar2008 jun2008 sep2008 dec2008 mar2009 jun2009 sep2009 dec2009 mar2010 jun2010 sep2010;set a4;run;

proc sql;

create table a5 as

select a.*,b.*

from work.funds as a left join work.a4 as b

on a.code=b.code;

quit;

data &st;

set WORK.a5;

array vol(63) mar1995 --sep2010;

array si (62) $  si1-si62;

do i= 2 to 63;

if vol(i) > vol(i-1) then si(i-1)='+';

else if vol(i) <  vol(i-1) then si(i-1)='-';

else si(i-1)='&';

end;

RUN;

%mend;

%test(ACERALIA)

Costasg
Calcite | Level 5

Thank you Linlin,

The macro works, but what i would like to do is not to have to change the Stock name each time, cause there could be thousands of stocks. Is there any way that SAS can do it automatically for each stock without me changing the stock name all the time? Maybe if i create a file with all stock names and then SAS looks at this file?

Linlin
Lapis Lazuli | Level 10

Hi,

I tried the sample code and it works:

title;

data stocks;

input st $;

cards;

ibm

ge

co

c

jmp

bp

;

/* put all your stocks in macro variable allstock */

proc sql noprint;

  select st into :allstock separated by ' '

    from stocks;

quit;

%macro test();

%do i=1 %to %sysfunc(countw(&allstock));

%let st=%scan(&allstock,&i);

proc print data=stocks;

   where st="&st";

   title from stock "&st";

   run;

  %end;

%mend;

%test

/***************************************************/

for your case please try the code below:

data stocks;

input st $;

cards;

ibm

ge

co

c

jmp

bp

;

/* put all your stocks in macro variable allstock */

proc sql noprint;

  select st into :allstock separated by ' '

    from stocks;

quit;

%macro test();

%do i=1 %to %sysfunc(countw(&allstock));

%let st=%scan(&allstock,&i);

proc sql;

create table C as

select*

from work.data

where stock = "&st";

quit;

proc sql;

create table b as

select date,code,value

from c;

quit;

proc sql;

create table f as

select a.*,b.*

from work.Date as a left join work.b as b

on a.date=b.date;

quit;

proc sort data=work.f nodupkey;by code date;run;

data a; set f; format date monyy7.; run;

proc sql; create table a_ as select distinct date,0.1 as code from a; quit;

data a2; set a a_;run;

proc sort data= a2; by code date;run;

proc transpose data=a2 out=a3(where=(code^=0.1));

by code;

id date;

var value;

run;

data work.a4;

set work.a3;

drop _NAME_;

run;

Data old;

Code=0 Mar1995=1 jun1995=2 sep1995=3 dec1995=4 mar1996=5 jun1996=6 sep1996=7 dec1996=8 mar1997=9 jun1997=10 sep1997=11 dec1997=12 mar1998=13 jun1998=14 sep1998=15 dec1998=16 mar1999=17 jun1999=18 sep1999=19 dec1999=20 mar2000=21 jun2000=22 sep2000=23 dec2000=24 mar2001=25 jun2001=26 sep2001=27 dec2001=28 mar2002=29 jun2002=30 sep2002=31 dec2002=32 mar2003=33 jun2003=34 sep2003=35 dec2003=36 mar2004=37 jun2004=38 sep2004=39 dec2004=40 mar2005=41 jun2005=42 sep2005=43 dec2005=44 mar2006=45 jun2006=46 sep2006=47 dec2006=48 mar2007=49 jun2007=50 sep2007=51 dec2007=52 mar2008=53 jun2008=54 sep2008=55 dec2008=56 mar2009=57 jun2009=58 sep2009=59 dec2009=60 mar2010=61 jun2010=62 sep2010=63 64;run;

Data new;

retain code Mar1995 jun1995 sep1995 dec1995 mar1996 jun1996 sep1996 dec1996 mar1997 jun1997 sep1997 dec1997 mar1998 jun1998 sep1998 dec1998 mar1999 jun1999 sep1999 dec1999 mar2000 jun2000 sep2000 dec2000 mar2001 jun2001 sep2001 dec2001 mar2002 jun2002 sep2002 dec2002 mar2003 jun2003 sep2003 dec2003 mar2004 jun2004 sep2004 dec2004 mar2005 jun2005 sep2005 dec2005 mar2006 jun2006 sep2006 dec2006 mar2007 jun2007 sep2007 dec2007 mar2008 jun2008 sep2008 dec2008 mar2009 jun2009 sep2009 dec2009 mar2010 jun2010 sep2010;set a4;run;

proc sql;

create table a5 as

select a.*,b.*

from work.funds as a left join work.a4 as b

on a.code=b.code;

quit;

data &st;

set WORK.a5;

array vol(63) mar1995 --sep2010;

array si (62) $  si1-si62;

do i= 2 to 63;

if vol(i) > vol(i-1) then si(i-1)='+';

else if vol(i) <  vol(i-1) then si(i-1)='-';

else si(i-1)='&';

end;

RUN;

  %end;

%mend;

%test

Linlin

Costasg
Calcite | Level 5

Many thanks Linlin, it works great!!!

May I ask one more question? Is there any macro that I can use to save all these files (in excel format) into one folder in my computer?

Many thanks again,

Costas

Linlin
Lapis Lazuli | Level 10

Hi Coastas,

I would follow Art and Astounding's suggestion to provide some sample data and your desired output. you will get a lot of good ideas and alternate solutions. there are a lot of  very talented experts on the forum and I have learned a lot from them.

for your question, the code below creates an excel file for each stock. Please modify the red part.

please ignore the warnings:

/*WARNING: File BP.bp.DATA does not exist.

WARNING: Table BP.bp has not been dropped.*/

data stocks;

input st $;

cards;

ibm

ge

co

c

jmp

bp

;

/* put all your stocks in macro variable allstock */

proc sql noprint;

  select st into :allstock separated by ' '

    from stocks;

quit;

%macro test();

%do i=1 %to %sysfunc(countw(&allstock));

%let st=%scan(&allstock,&i);

data &st;

   set stocks(where=(st="&st"));

   run;

%end;

%mend;

%test

%macro test2;

%do i=1 %to %sysfunc(countw(&allstock));

%let st=%scan(&allstock,&i);

/* using your own folder */

  libname &st "c:\temp\forum\&st..xls";

   proc sql;drop table &st..&st;quit;

    data &st..&st;

     set &st;

   run;

  libname &st clear;

%end;

  %mend;

  %test2

Astounding
PROC Star

Costasg,

Both previous posters are likely to be 100% correct here.  You may find a program that will do what you ask.  But it also may take 500 times as long to run as an alternate solution.  Give us some information about your starting data and your desired result and you will get additional suggestions.

Good luck.

Costasg
Calcite | Level 5

Thanks again Linlin. Ok, I am attaching some sample files.

Data: a small sample of my data

Date: a list of all dates (as not all funds trade on all dates)

Funds: the codes of all funds

Output: this is the final output for each stock (Ideally, I would like to keep only the new variables (s1,s2....) but rename them with the names of the old ones starting with the second date)

And here again is the code I apply:

proc sql;
create table C as
select*
from work.data
where stock = "ACERALIA";
quit;

proc sql;
create table b as
select date,code,value
from c;
quit;

proc sql;
create table f as
select a.*,b.*
from work.Date as a left join work.b as b
on a.date=b.date;
quit;

proc sort data=work.f nodupkey;by code date;run;
data a; set f; format date monyy7.; run;
proc sql; create table a_ as select distinct date,0.1 as code from a; quit;

data a2; set a a_;run;
proc sort data= a2; by code date;run;
proc transpose data=a2 out=a3(where=(code^=0.1));
by code;
id date;
var value;
run;
data work.a4;
set work.a3;
drop _NAME_;
run;
Data old;
Code=0 Mar1995=1 jun1995=2 sep1995=3 dec1995=4 mar1996=5 jun1996=6 sep1996=7 dec1996=8 mar1997=9 jun1997=10 sep1997=11 dec1997=12 mar1998=13 jun1998=14 sep1998=15 dec1998=16 mar1999=17 jun1999=18 sep1999=19 dec1999=20 mar2000=21 jun2000=22 sep2000=23 dec2000=24 mar2001=25 jun2001=26 sep2001=27 dec2001=28 mar2002=29 jun2002=30 sep2002=31 dec2002=32 mar2003=33 jun2003=34 sep2003=35 dec2003=36 mar2004=37 jun2004=38 sep2004=39 dec2004=40 mar2005=41 jun2005=42 sep2005=43 dec2005=44 mar2006=45 jun2006=46 sep2006=47 dec2006=48 mar2007=49 jun2007=50 sep2007=51 dec2007=52 mar2008=53 jun2008=54 sep2008=55 dec2008=56 mar2009=57 jun2009=58 sep2009=59 dec2009=60 mar2010=61 jun2010=62 sep2010=63 64;run;

Data new;
retain code Mar1995 jun1995 sep1995 dec1995 mar1996 jun1996 sep1996 dec1996 mar1997 jun1997 sep1997 dec1997 mar1998 jun1998 sep1998 dec1998 mar1999 jun1999 sep1999 dec1999 mar2000 jun2000 sep2000 dec2000 mar2001 jun2001 sep2001 dec2001 mar2002 jun2002 sep2002 dec2002 mar2003 jun2003 sep2003 dec2003 mar2004 jun2004 sep2004 dec2004 mar2005 jun2005 sep2005 dec2005 mar2006 jun2006 sep2006 dec2006 mar2007 jun2007 sep2007 dec2007 mar2008 jun2008 sep2008 dec2008 mar2009 jun2009 sep2009 dec2009 mar2010 jun2010 sep2010;set a4;run;


proc sql;
create table a5 as
select a.*,b.*
from work.funds as a left join work.new as b
on a.code=b.code;
quit;

data asigns;
set WORK.a5;
array vol(63) mar1995 --sep2010;
array si (62) $  si1-si62;
do i= 2 to 63;
if vol(i) > vol(i-1) then si(i-1)='+';
else if vol(i) <  vol(i-1) then si(i-1)='-';
else si(i-1)='&';
end;
RUN;

Linlin
Lapis Lazuli | Level 10

Since Art and Astounding have not responded yet. I will give a try:

proc sql;

create table b as

  select date,code,value

    from work.data

       where stock = "ACERALIA";

create table a as

  select a.date format=monyy7. ,code,value

    from work.Date as a left join work.b as b

on a.date=b.date;

quit;

proc sort data=work.a nodupkey;by code date;run;

proc sql; create table a_ as select distinct date,0.1 as code from a; quit;

data a2; set a a_;run;

proc sort data= a2; by code date;run;

proc transpose data=a2 out=a4(where=(code^=0.1) drop=_name_);

by code;

id date;

var value;

run;

proc sql;

create table a5 as

select a.*,b.*

from work.funds as a left join work.a4 as b

on a.code=b.code;

quit;

Data a5;

retain code Mar1995 jun1995 sep1995 dec1995 mar1996 jun1996 sep1996 dec1996 mar1997 jun1997

sep1997 dec1997 mar1998 jun1998 sep1998 dec1998 mar1999 jun1999 sep1999 dec1999 mar2000

jun2000 sep2000 dec2000 mar2001 jun2001 sep2001 dec2001 mar2002 jun2002 sep2002 dec2002

mar2003 jun2003 sep2003 dec2003 mar2004 jun2004 sep2004 dec2004 mar2005 jun2005 sep2005

dec2005 mar2006 jun2006 sep2006 dec2006 mar2007 jun2007 sep2007 dec2007 mar2008 jun2008

sep2008 dec2008 mar2009 jun2009 sep2009 dec2009 mar2010 jun2010 sep2010;

set a5;

run;

data asigns;

set WORK.a5;

array vol(63) mar1995 --sep2010;

array si (62) $  si1-si62;

do i= 2 to 63;

if vol(i) > vol(i-1) then si(i-1)='+';

else if vol(i) <  vol(i-1) then si(i-1)='-';

else si(i-1)='&';

end;

RUN;

Costasg
Calcite | Level 5

Many thanks again Linlin Smiley Happy

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 10 replies
  • 1040 views
  • 6 likes
  • 4 in conversation