creating macro variable for multiple value

[woo]

Hello friends please help me to create two or multiple macro variable for existing dataset.

I have dataset "a" where variable "date" has two values as mentioned below. I want to create two macro variable for this two value, how can i create it? I don't know how can i replicate do statement here...Thanks...Also sometime this "date" variable has more than 2 values...so in that case i will need to create more than 2 macro variable....

date

20141002

20141001

like &date1 should be 20141002

and &date2 should be 20101001

[stat_sas]

proc sql noprint;
select date
into :date1 - :date2
from have;
quit;

%put &date1 &date2;

[woo]

Thanks State but what if we don't know date2 (i mean if date has more than one value)...and can we do this using data step?

[Tom]

To create macro variables in a data step use the CALL SYMPUTX() function.  You will need a counter variable to help with generating the macro variable name. For a simple case you could use the automatic loop counter _N_.

data _null_;

  set sashelp.class end=eof;

  call symputx(cats('AGE',_n_),age);

  if eof then call symputx('N',_n_);

run;

[Tom]

You can use PROC SQL to convert values from multiple observations into multiple macro variables.  Use automatic macro variable SQLOBS to let you know how many are actually created.

proc sql noprint ;

* Before SAS 9.3 ;

select age into :age1-:age99999 from sashelp.class;

* SAS 9.3 or newer ;

select age into :age1- from sashelp.class;

%let n=&sqlobs;

quit;

But the big question is why would you WANT to do that?  Working with many macros variables can become very confusing.  There might be a better way to code your problem that will not require them.

[woo]

Thanks Tom, we used to receive file on daily bases and we don't know how many unique date out there in the file so i am reading file in and then using below code to send out email to team...

proc sql noprint;

select date into :date1- :date9999 from have;

%let n=&sqlobs;

quit;

filename mymail email

                         subject="no. of dif date in file"

                         to=("email1" "email2" "emailn... ");

           data _null_;

                file mymail;

                put "hello team, we have date&n data into the file";

                /*this doesn't look good coz i am not sure how i can show macro variables (may be                       more than 2 or 3 date in email message*/

           run;

[Tom]

You can let the DATA step handle that.

proc sort data=HAVE (keep=date) nodupkey out=udates;

  by date ;

run;

filename mymail email subject="no. of dif date in file" to=("email1" "email2" "emailn... ");

data _null_;

  set have ;

  file mymail;

  if _n_=1 then put "hello team, we have data for the following dates:";

  put date ;

run;

[woo]

i can still use your data step to create it but challenge for me is how to show two or more different date in email message...???

[woo]

Thanks a lot Tom - i got that...

sorry to drag you into this issue but have issue in how email shows message:

i am also including other macro variable value into message (&x_date and &y_date) /*&x_date=29sep14 and &y_date=28sep14*/

/*you suggested resolves to*/

20140930

20141001

/*so final filename statement looks like this*/

filename mymail email

      subject="xxxxxxxxxxxxxxxxxxxxxxxxxxxx"

      to= ("xxxxxxxxxxxxxxxxx");

        data _null_;

        set have;

              file mymail;

              if _n_=1 then put "hello team, we have data for the following dates:";

              put date;

              put "&file1 with &x_date"; /*my additional  macro variable*/

              put "&file2 with &y_date"; /*my additional  macro variable*/

  run;

email shows like this:

____________________________________________________

"hello team, we have data for the following dates:";

20140930

file1 with 29sep14

file2 with 28sep14

20141001

file1 with 29sep14

file2 with 28sep14

___________________________________________________

-----------------------------------------

it should show dates only once, like this, right?:

"hello team, we have data for the following dates:";

20140930

20141001

file1 with 29sep14

file2 with 28sep14

----------------------------------------

[Tom]

Not sure I follow. Looks like a standard data step report.   Get the dataset you want to drive the report created before you start the data step that writes it. You can debug by writing to a file instead of EMAIL.

data have ;

  length filename $50 date 8 other $10;

  informat date date9.;

  format date date9. ;

  input filename date other;

cards;

file1 28SEP2014 Fred

file1 28SEP2014 Sam

file1 28SEP2014 Joe

file2 30SEP2014 Eric

file2 01OCT2014 Rich

run;

data _null_;

  file print ;

  if _n_=1 then put 'Greetings';

  if eof then put 'Salutations';

  set have end=eof ;

  by filename date;

  if first.filename then put filename ;

  if first.date then put @5 date ;

run;

Greetings

file1

    28SEP2014

file2

    30SEP2014

    01OCT2014

Salutations

[woo]

Tom i have following code;

%let ext=txt;

%let dir1=w:\woo\testdir1\;

%let dir2=w:\woo\testdir2\;

data test1;

length fname1 $200;

infile "dir &dir1.*.&ext /tw" pipe truncover lrecl=3000;

input fname $200.;

run;

data test2;

length fname2 $200;

infile "dir &dir2.*.&ext /tw" pipe truncover lrecl=3000;

input fname $200.;

run;

THEN SOME DATA STEPS TO GET MODIFIED DATE FOR FILE1 AND FILE2

&file1 with &x_date; /*file1 with 29sep14*/

&file2 with &y_date; /*file2 with 28sep14*/

/*your logic*/

/*i am trying to include my PUT statement along with your logic*/

proc sort data=HAVE (keep=date) nodupkey out=udates;

  by date ;

run;

filename mymail email subject="no. of dif date in file" to=("email1" "email2" "emailn... ");

data _null_;

  set have ;

  file mymail;

  if _n_=1 then put "hello team, we have data for the following dates:";

  put date ;

   put &file1 with &x_date;

   put &file2 with &y_date;

run;

[Tom]

You can use a single DIR command to list the files in both directories and so a single data step to read the file names.

I do not trust DIR command to subset by extension because it only matches three characters. So *.SAS also returns .SAS7BDAT and *.XLS also returns *.XLSX.  You can subste in the SAS data step instead.

%let dir1=c:\downloads;

%let dir2=c:\temp;

%let ext=txt ;

data files ;

  infile "dir /a-d /tw &dir1 &dir2" pipe truncover lrecl=500 ;

  length date time size 8 fname dname $256. ;

  format date yymmdd10. time tod5. ;

  informat size comma32.;

  retain dname;

  input @ ;

  if _infile_=:' Directory' then input dname dname dname $256. ;

  if substr(_infile_,1,1)=' ' then delete;

  input date mmddyy10. time time10. size fname $256. ;

  if upcase(scan(fname,-1,'.'))=upcase("&ext") then output;

run;

data _null_;

  file print ;

  if _n_=1 then put 'Here are the new files';

  if eof then put 'Found ' nobs :comma20. ' files';

  set files end=eof nobs=nobs;

  by dname fname ;

  if first.dname then put DNAME=;

put fname= date= time= size=:comma20.;

run;

[woo]

Hello Tom, thanks for your detail code but i am still struggling to get this done. So i have done following things. I just need help in using my macro value to send out email to team.

/*Create "control tbl" for message */

Dataset name "Control_tbl"

Alert    Message

1          Hello team, today we received files as below

2          file_1 arrived today with

3          file_1 has not arrived today

4          file_2 arrived today with

5          file_2 has not arrived today

6          file_3 arrived today with

7          file_3 has not arrived today

8          file_4 arrived today with

9          file_4 has not arrived today

/*Read all txt files for DIR1*/

Dataset name "A"

/*Read all txt files for DIR2*/

Dataset name "B"

/*step1*/

data c;

set a;

if find specific file;

upload_date=.......;

run;

proc sort data=c;

by descending upload_date;

run;

data c;

set c (obs=1);

call sumput (upload_date1, put(upload_date, date9.));

runl

/*established macro variable &upload_date_1 which has value for lets say today's date (alert=2 message goes out from control table) OR if file not present then this macro will not resolve so alert=3 message goes out from control table*/

/*step2*/

dataset name d

same as step 1 for different specific file and created &upload_date_2 which has value for lets say today's date (alert=4 message goes out from control table) OR if file not present then this macro will not resolve so alert=5 message goes out from control table

/*************** i completed work until here*********since know there will be only one date for file **************Need help from here please*******************/

/*step3*/

- read specific one particular file in from dataset A which has lot dif. text file (key var=date)

- pull out distinct date using proc sql

- now we don't know how many distinct date here will be - may be 1 or 2 or 3 so need to create macro variable for all available distinct "date"

Alert=6 message goes out from control table and if file not there on directory then alert=7  message goes out

/*step4*/ SAME CASE AS STEP3 for different specific file

Alert=8 message goes out from control table and if file not there on directory then alert=9 message goes out

END

/*This is the sample message*/

Hello team, today we received files as below

file_1 arrived today with 06Oct14

file_2 has not arrived today

file_3 arrived today with 04Oct14, 05Oct14, 06Oct14

file_4 arrived today with 05Oct14, 06Oct14

/*does this idea looks feasible to you - please let me know when you get chance.*/

Thanks!

[Tom]

I am not sure I get your logic.  How do you know when a file doesn't exist?  Do you have a list somewhere of expected files?

How can you have the same file on multiple dates?  Is it possible that your list of expected files is instead a list of patterns of file names?

Either way just create the dataset needed to produce the report you want. Then writing the report is easy and does not involve macro variables.

data expected ;

  input file_prefix $ @@;

cards;

A B C D E F G H

;;;;

proc sort; by file_prefix; run;

data found ;

  input filename $ date yymmdd10. ;

  file_prefix = substr(filename,1,1);

  format date yymmdd10. ;

cards;

A1.txt 2014-10-06

C1.txt 2014-10-04

C2.txt 2014-10-05

C3.txt 2014-10-06

D1.txt 2014-10-05

D2.txt 2014-10-06

;;;;

proc sort; by file_prefix filename date ; run;

data report ;

  merge expected found ;

  by file_prefix ;

run;

ods _all_ close;

ods listing ;

options nocenter ;

data _null_;

  file print ;

  if _n_=1 then put 'Hello team, today we received files as below' / ;

  if eof then do;

     if nobs=0 then put 'No files expected today.';

     else put nobs 'total file/dates arrived today.' ;

  end;

  set report nobs=nobs end=eof;

  by file_prefix ;

  if first.file_prefix then put 'File ' FILE_PREFIX @ ;

  if date=. then put 'has not arrived today' @;

  else do;

    if first.file_prefix then put 'arrived today with ' @;

    put date @ ;

  end;

  if last.file_prefix then put '.';

run;

Hello team, today we received files as below

File A arrived today with 2014-10-06 .

File B has not arrived today.

File C arrived today with 2014-10-04 2014-10-05 2014-10-06 .

File D arrived today with 2014-10-05 2014-10-06 .

File E has not arrived today.

File F has not arrived today.

File G has not arrived today.

File H has not arrived today.

11 total file/dates arrived today.

[woo]

K Tom my be i didn't represent very well...

forget about that - forget about control table and all

Here is the real coding

options mprint mlogic symbolgen;

%let ext=txt;

%let dir1=w:\data\in\;

%let dir2=w:\shares\woo\;

data f_list1;

length fname1 $200;

infile "dir &dir1.*.&ext /tw " pipe truncover lrecl=3000 ;

input fname1 $200.;

run;

data f_list2;

length fname2 $200;

infile "dir &dir2.*.&ext /tw " pipe truncover lrecl=3000 ;

input fname2 $200.;

run;

data a;

format upload_date date.;

set f_list1;

if find(upcase(fname1),upcase("k_first_file"))>0;

r_name=substr(upcase(fname1),find(upcase(fname1),upcase("k_first_file")));

upload_date=input(substr(fname1,1,10),mmddyy10.);

run;

proc sort data=a;

by descending upload_date;

run;

data a ;

set a(obs=1);

call symput("upload_1",put(upload_date, date9.));

run;

data b;

format upload_date date.;

set f_list2;

if find(upcase(fname2),upcase("k_second_file"))>0;

r_name=substr(upcase(fname2),find(upcase(fname2),upcase("k_second_file")));

upload_date=input(substr(fname2,1,10),mmddyy10.);

run;

proc sort data=b;

by descending upload_date;

run;

data b;

set b(obs=1);

call symput("upload_2",put(upload_date, date9.));

run;

data c;

format upload_date date.;

set f_list1;

if find(upcase(fname1),upcase("K_third_file"))>0;

f_name=substr(upcase(fname1),find(upcase(fname1),upcase("K_third_file")));

r_name=(substr(fname1,40,150)); /*pulling full name for file "K_third_file" to use it later in filename statement*/

r_name=trim(left(r_name));

upload_date=input(substr(fname1,1,10),mmddyy10.);

run;

proc sort data=c;

by descending upload_date;

run;

data c;

set c (obs=1);

call symput("upload_3",put(upload_date, date9.));

call symput("r_name",r_name);

run;

/*Read "K_third_file" file in and figure how many distinct date there in file*/

filename xyzin "&dir1.&r_name.";

data file_in_1;

infile xyzin dsd delimiter='|' firstobs=2;

length date $ 20

       x $ 10

       y $ 10

    z $ 10

input  date $

       x $

       y $

    z$

    ;

run;

proc sql;

create table dist_date_c as select distinct date from file_in_1;

quit;

/*i stuck from here*/

/*here lets say table "c_uni_date" has 3 distinct date which is not a case all time - could be 1 distinct date or 2 or 3*/

/*so need to create macro variable for all 3 dates (if 3 distinct date) and need to show in email...*/

/*not sure but we can do this - we can create one new macro variable X here which include n number of date macro variable (3 in this case) so that we can mentioned &x in message to show 3 different macro variable which would resolve in 3 dif. date...like "&&x&daten"=date1 date2 date3*/

data d;

format upload_date date.;

set f_list1;

if find(upcase(fname1),upcase("k_forth_file"))>0;

f_name=substr(upcase(fname1),find(upcase(fname1),upcase("k_forth_file")));

m_name=(substr(fname1,40,150)); /*pulling full name for file "K_forth_file" to use it later in filename statement*/

m_name=trim(left(m_name));

upload_date=input(substr(fname1,1,10),mmddyy10.);

run;

proc sort data=d;

by descending upload_date;

run;

data d;

set d(obs=1);

call symput("upload_4",put(upload_date, date9.));

call symput("m_name",m_name);

run;

filename abcin "&dir2.&m_name.";

data file_in_2;

infile abcin dsd delimiter='|' firstobs=2;

length date $ 20

       a $ 10

       b $ 10

    c $ 10

input  date $

       a $

       b $

    c $

    ;

run;

proc sql;

create table uni_date_d as select distinct date from file_in_2;

quit;

/*here lets say table "c_uni_date" has 3 distinct date which is not a case all time - could be 1 distinct date or 2 or 3*/

/*same thing here - we can create one new macro variable X here which include n number of date macro variable (3 in this case) so that we can mentioned &x in message to show 3 different macro variable which would resolve in 3 dif. date...like "&&x&daten"=date1 date2 date3*/

%let file1=k_first_file;

%let file2=k_second_file;

%let file3=k_third_file;

%let file4=k_forth_file;

filename mymail email

      subject="All file detail"

      to= ("xyz@yahoo.com"); 

        data _null_;

  set a;

  set b;

  set c;

  set d;

        file mymail;

        put "&file1 uploaded on &upload_1";

  put " ";

     put "&file2 uploaded on &upload_2";

     put " ";

     put "&file3 uploaded on &upload_3 with distinct date: /*numbers of distinct date goes here - may be 1 or 2 or 3*/";

     put " ";

  put "&file4 uploaded on &upload_4 with distinct date: /*numbers of distinct date goes here - may be 1 or 2 or 3*/";

  run;