Hello everyone, how to past variable to variable ?

[liod21]

My data is :

data notes;

INPUT isin$ User first_date$ last_date$;

CARDS;

US91232N1081 24 09APR2009 16JUN2009

US91232N1081 31 14OCT2009 06APR2010

FR0010361683 83 13OCT2010 19AUG2011

;

run;

And i want to past to firstelement to secondelement ... So, I want to travel like an array. I want 4 macrovariables et i want that macrovariables change everytime. I want first :

%let var1='US91232N1081';

%let var2=24;

%let var3=09APR2009;

%let var4=16JUN2009;

and after i want :

%let var1='US91232N1081';

%let var2=31;

%let var3=14OCT2009;

%let var4=06APR2010;

I want a loop and automation because i use after this macrovariables for a code :

data test ;

set Memoire.Hlc_dejean_sort;

where (isin=&var1. and Jour>=&Var3) and (isin=&var1. and Jour<=var4);

run;

Thanks,

[PaigeMiller]

I don't understand what "past to firstelement to secondelement" means, and your simple example doesn't seem to shed light on this. I don't know what firstelement means; I don't know what secondelement means; and I have no idea why macro variables are needed here, as data is much better handled as data, rather than handling it via macro variables.

Please provide a lot more detail. Please explain what you intend to do with this data that macro variables are needed. Please explain the big picture (not just the details of how the macros are created) do not skip this.

Also, reading in dates as character strings is almost always a poor choice; dates should be numeric and proper SAS date values, you can read them in as numeric as follows:

data notes;
INPUT isin$ User first_date :date9. last_date :date9.;
format first_date last_date date9.;
CARDS;
US91232N1081 24 09APR2009 16JUN2009
US91232N1081 31 14OCT2009 06APR2010
FR0010361683 83 13OCT2010 19AUG2011
;

[liod21]

Ok, I take more details. 

I have a table :

data notes;
INPUT isin$ User first_date :date9. last_date :date9.;
format first_date last_date date9.;
CARDS;
US91232N1081 24 09APR2009 16JUN2009
US91232N1081 31 14OCT2009 06APR2010
FR0010361683 83 13OCT2010 19AUG2011
;

And after I have a programme that combinate other table and every time, i look the table notes and i write every ligne. I want a loop that discover my table notes and read every variable and put every time the variable in the different macrovariable. For example, i want first that my programme put US91232N1081 in var1; 24 in var 2; 09APR2009 in var3 and 16JUN2009 in var 4 and run the programme data test. Second, i want that my programme put US91232N1081 in var1; 31 in var 2;14OCT2009  in var3 and 06APR2010 in var 4 and so on for every lines. Because my program put after a sheet of excel and if it's possible not to write every variable on macrovariable, it' will be very cool. I'm sure that possible with a loop to put every seconde variable in macrovariable but i don't know how to do.

main code :

%let var1='US91232N1081';

%let var2=24;

%let var3=09APR2009;

%let var4=16JUN2009;

data test ;

set Memoire.Hlc_dejean_sort;

where (isin=&var1. and Jour>=&var3) and (isin=&var1. and Jour<=&var4);

run;

data test2 (keep= isin Jour TransType ExecQty ExecPrice_EUR);

set Memoire.Trade_stock_sort_dejean_fin;

if isin=&var1. and user=&var2.;

run;

Proc export data=Work.newtest

OUTFILE="ok_STOCK.xlsx"

      dbms=xlsx replace;

      sheet="BE0003562700-S-24";

      run;

[PaigeMiller]

I have provided an alternative method that does not involve macro variables and does not involve explicit looping in an earlier reply.

In general, this is a poor practice to split data sets up like this, usually there are better methods; and you have not explained why you want this. Please explain why you want a data set to be split up like this.

[Tom · Accepted solution]

You seem to be describing a need for a MACRO

%macro mymacro(var1,var2,var3,var4);
data test ....
%mend mymacro;

Note: I am not sure why you are using such generic names for your macro variables, more descriptive names will make the code easier to read.

That you can then call once for each observation in your driver dataset.

data _null_;
  set notes;
  call execute(cats(
       '%nrstr(%mymacro)'
     , '(', quote(trim(isin),"'")
     , ',', user
     , ',', put(first_date,date9.)
     , ',', put(last_date,date9.)
     , ')'
  ));
run;

[liod21]

Thanks @Tom and @PaigeMiller. I find a solution. I put my table notes in notepad and after i change the syntax and put this in my code with macro like that :

%MACRO Creer_Var(var1,var2,var3,var4);
data test;
set Work.datum_product;
where (isin=&var1. and Jour>="&var3."d) and (isin=&var1. and Jour<="&var4."d);
run;

%MEND;
%Creer_Var('US91232N1081',24,09APR2009,16JUN2009)
%Creer_Var('US91232N1081',31,14OCT2009,06APR2010)
%Creer_Var('FR0010361683',83,13OCT2010,19AUG2011)
%Creer_Var('FR0010408799',83,17OCT2011,08MAR2012)
%Creer_Var('FR0010361683',104,06AUG2009,19NOV2010)
%Creer_Var('IE00B02KXH56',104,23DEC2005,15JAN2008)
%Creer_Var('IE00B02KXK85',104,22FEB2006,15MAR2011)
%Creer_Var('IE00B0M63391',104,02DEC2005,03SEP2010)
%Creer_Var('IE00B0M63516',104,08DEC2005,19JAN2012)

[PaigeMiller]

That's a lot of unnecessary copying and pasting of data compared to the solution from @Tom or my suggestion, in which there is no need to copy and paste the data from one location to another.

[PaigeMiller]

I want a loop and automation because i use after this macrovariables for a code :

data test ;

set Memoire.Hlc_dejean_sort;

where (isin=&var1. and Jour>=&Var3) and (isin=&var1. and Jour<=var4);

run;

Maybe this is what you need (UNTESTED CODE):

data notes;
INPUT isin$ User first_date :date9. last_date :date9.;
call execute('data test'||left(_n_)||'; set memoire.hlc_dejean_sort; where (isin='||isin||
' and jour>='||first_date||') and (isin='||isin||' and jour<='||last_date||');');
CARDS;
US91232N1081 24 09APR2009 16JUN2009
US91232N1081 31 14OCT2009 06APR2010
FR0010361683 83 13OCT2010 19AUG2011
;

No macro variables needed to hold the data set variable values; no explicit looping.

[liod21]

Thanks for answers but it's not what i want. I will explain better. 

I have three tables : datum_product ; notes;summary. So they are 

- First 

datum_product that contains all the product numbers with the price and the date between 2003 and 2012. They are 1000 different product_numbers in the table. 

data datum_product;

INPUT product_numbers $ date $ price ;

CARDS;

US91232N1081  01APR2003 6.5

US91232N1081  02APR2003 7.2

US91232N1081  03APR2003 6.8

US91232N1081  04APR2003 8.1

.......

US91232N1081 01APR2012 11

FR0010361683 01APR2003 14.1

FR0010361683 02APR2003 13.9

FR0010361683 03APR2003 13.9

FR0010361683 04APR2003 12

.......

FR0010361683  13OCT2012 14.3

.......

;

run;

- second table is notes that gives the first date the user purchased the product and the last date.

data notes;

INPUT product_number$ User first_date$ last_date$;

CARDS;

US91232N1081 24 09APR2009 16JUN2009

US91232N1081 31 14OCT2009 06APR2010

FR0010361683 83 13OCT2010 19AUG2011

;

run;

-Thirst table is summary that contains all transactions by user and by product_number, the quantity and the price of the purchase and the day.

data summary;

Input product_number $  user date $ Quantity Price_purchase;

CARDS;

US91232N1081 24 09APR2009 20 8.6

US91232N1081 24 15MAY2009 12 9.4

US91232N1081 24 16JUN2009 14 9.6

US91232N1081 31 14OCT2009 40 9.86

US91232N1081 31 26OCT2009 3 9.75

US91232N1081 31 15FEB2010 33 9.86

US91232N1081 31 06APR2010 25 9.8

.....

run;

What i Want ? 

The most importante data is in notes. First line --> The user 24 bought the product number "US91232N1081" fort the first time the  09APR2009 and bought the last time the 16JUN2009. I put 4 macrovariable because i write every time in different space to put the macrovariable. 

my main code is :

%let var1='US91232N1081 ';

%let var2=24;

%let var3='09APR2009'

%let var4='16JUN2009'

data test ;

set Work.datum_product;

where (product_number=&var1. and date>=%var3.) and (product_number=&var1. and date<=%var4.);

run;

data test2;

set Work.Summary;

if product_number=&var1. and user=&var2.;

run;

%Macro Fusion_left_join(table_gauche=,

                                    table_droite=,

                                    colonne_fusion_gauche=,

                                    colonne_fusion_droite=);

PROC SQL;

Create Table final as

Select * FROM &table_gauche AS A

      LEFT Join &table_droite AS B

on A.&colonne_fusion_gauche.=B.&colonne_fusion_droite.;

QUIT;

%Mend Fusion_left_join;

%Fusion_left_join(table_gauche=Test,

                                    table_droite=Test2,

                                    colonne_fusion_gauche=date,

                                    colonne_fusion_droite=date);

Proc export data=Work.final

OUTFILE="\\Product.xlsx"

      dbms=xlsx replace;

      sheet="&var1. -E- &var2.";

      run;

So when i run, i have the good result. Second, i write the line 2 of notes in my macro-variable so i just change macrovariable like that :

%let var1='US91232N1081 ';

%let var2=31;

%let var3='142009'

%let var4='06APR20010' and i run the code and it's work but i have more than 600 lines on the table "notes"   so if it's possible not to write every time each line in my macrovariable and put a loop that the code turn everytime with each line and put each sheet of excel. 

i hope I have more clear. Thanks for the help. 

[liod21]

I can explain that more clear. 

I have 3 different tables :

1) datum_product that give the product_number; the date and the price of the product during the date between 01APR2003 and 01APR2012. So i have the price of 1000 product between 01APR2003 and 01APR2012

data datum_product;

input product_number $ date $ price;

cards;

US91232N108 01APR2003 7.5

US91232N108 02APR2003 7.48

US91232N108 03APR2003 7.53

.....

US91232N108 01APR2012 9.3

FR0010361683 01APR2003 17.2

FR0010361683 02APR2003 17.25

.....

FR0010361683 01APR2012 14.1

2) Summary --> Every line, I have the  user; the product that he bought; the date; the quantity and the price 

data Summary;

input product_number $ user date$ quantity price;

cards;

US91232N108 24 09APR2009 25 7.5

US91232N108 24 12MAY2009 10 7.8

US91232N108 24 16JUN2009 17 7.63

US91232N1081 31 14OCT2009 8 7.69 

US91232N1081 31 05NOV2009 10 7.69 

US91232N1081 31 07NOV2009 2 7.69 

US91232N1081 31 14FEB2009 18 7.69 

US91232N1081 31 06APR2010 3 8.2

......

3) notes --> that give the user and the product he bought and give the first date and the last date he bought this product.

data notes;

INPUT isin$ User first_date$ last_date$;

CARDS;

US91232N1081 24 09APR2009 16JUN2009

US91232N1081 31 14OCT2009 06APR2010

FR0010361683 83 13OCT2010 19AUG2011

;

run

My most important dataline is in notes. I want to use every line in my main code but i write different data in each line in 4 macrovariables and i have more than 400 lines in "notes" and if it's possible to find a solution not to write every time. 

so first time, my four macrovariables is : 

%let var1='US91232N1081';

%let var2=24;

%let var3= '09APR2009'

%let var4='16JUN2009'

and put this in my main code. Second run i have  

%let var1='US91232N1081';

%let var2=31;

%let var3= '14OCT2009'

%let var4='06APR2010'

my main code is :

%let var1='US91232N1081';

%let var2=31;

%let var3= '14OCT2009'

%let var4='06APR2010'

data test ;

set work.datum_product;

where (isin=&var1. and Jour>=&var3. and (isin=&var1. and Jour<=&var4.);

run;

data test2 

set Work.summary;

if isin=&var1. and user=&var2.;

run;

%Macro Fusion_left_join(table_gauche=,

                                    table_droite=,

                                    colonne_fusion_gauche=,

                                    colonne_fusion_droite=);

PROC SQL;

Create Table newtest as

Select * FROM &table_gauche AS A

      LEFT Join &table_droite AS B

on A.&colonne_fusion_gauche.=B.&colonne_fusion_droite.;

QUIT;

%Mend Fusion_left_join;

%Fusion_left_join(table_gauche=Test,

                                    table_droite=Test2,

                                    colonne_fusion_gauche=Jour,

                                    colonne_fusion_droite=Jour);

Proc export data=Work.newtest

OUTFILE="\\product.xlsx"

      dbms=xlsx replace;

      sheet="&var1. -E- &var2.";

      run;

I hope that i'm more clear. Thanks for the help.