topic Re: creating macro variable for multiple value in SAS Enterprise Guide

creating macro variable for multiple value

woo — Fri, 03 Oct 2014 22:18:04 GMT

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

Re: creating macro variable for multiple value

stat_sas — Fri, 03 Oct 2014 22:44:05 GMT

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

%put &date1 &date2;

Re: creating macro variable for multiple value

Tom — Fri, 03 Oct 2014 22:59:16 GMT

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.

Re: creating macro variable for multiple value

woo — Fri, 03 Oct 2014 22:59:34 GMT

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?

Re: creating macro variable for multiple value

Tom — Fri, 03 Oct 2014 23:10:08 GMT

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;

Re: creating macro variable for multiple value

woo — Fri, 03 Oct 2014 23:14:40 GMT

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;

Re: creating macro variable for multiple value

woo — Fri, 03 Oct 2014 23:26:56 GMT

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

Re: creating macro variable for multiple value

Tom — Fri, 03 Oct 2014 23:42:44 GMT

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;

Re: creating macro variable for multiple value

woo — Sat, 04 Oct 2014 00:28:14 GMT

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

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

Re: creating macro variable for multiple value

Tom — Sat, 04 Oct 2014 00:56:20 GMT

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

Re: creating macro variable for multiple value

woo — Sat, 04 Oct 2014 04:43:24 GMT

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;

Re: creating macro variable for multiple value

Tom — Sat, 04 Oct 2014 07:24:12 GMT

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;

Re: creating macro variable for multiple value

woo — Mon, 06 Oct 2014 18:22:33 GMT

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!

Re: creating macro variable for multiple value

Tom — Mon, 06 Oct 2014 20:19:43 GMT

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.

Re: creating macro variable for multiple value

woo — Mon, 06 Oct 2014 21:40:19 GMT

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 $

;

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;

Re: creating macro variable for multiple value

Tom — Mon, 06 Oct 2014 22:25:39 GMT

One question maybe answered. Are you getting the multiple dates from reading the content of the files rather than by taking the modified date of the files from the output of the DIR command?

I am still not sure how you are distinguishing the files. You seem to want to find the files that start with K_FIRST_FILE or K_SECOND_FILE etc.

Are those really the names? Or if not the real names is the list of names constant or are are you looking for a solution where the list of filenames (or are they just filename prefixes) are passed into the system? Is the list of names always 4 names long or do you want it flexible to handle different numbers of expected files?

Again to me the solution is to generate a data set with one row per file*date combination and use that to drive the report as in my previous post.

But if you want to try to stick to macro variables then you can use PROC SQL to create a single macro variable with the full list of dates (as long as it is not so long as to make the macro variable longer than the limit.). Basically in your program you would replace this code :

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;

with a single PROC SQL step.

proc sql noprint ;

%let upload_4=;

select distinct upload_date format=date9.

into :upload_4 separated by ', '

from d

order by date descending

;

quit;

Re: creating macro variable for multiple value

Tom — Mon, 06 Oct 2014 22:50:00 GMT

Is your problem really that you do not know how to read the contents of variable number of files from a directory?

Why not just read the files in and skip the directory contents?

data dates ;

length fname filename $200;

infile "&dir1\*.txt" dsd dlm='|' filename=fname eov=eov ;

input @;

filename=fname;

if _n_=1 or eov then input ;

input date date9. ;

format date date9.;

eov=0;

run;

proc sort data=dates nodupkey ;

by filename descending date ;

run;

data _null_;

set dates ;

by filename ;

if first.filename then put filename= ;

put date=;

run;

filename=c:\downloads\folder1\k_first_file.txt

date=05OCT2014

date=04OCT2014

date=03OCT2014

filename=c:\downloads\folder1\k_second_file.txt

date=05OCT2014

date=04OCT2014

date=03OCT2014

Re: creating macro variable for multiple value

woo — Tue, 07 Oct 2014 00:53:10 GMT

Thank you Tom.

Here is my problem in simple way ...

/*lets assume that i am receiving "|" separated "class" text file (sashelp.class) on daily bases from you and i don't know how many distinct age will be there on different day (assume we will have different distinct age) and i want to show third party that class file uploaded today where we have n number of distinct age and those age are: 11 12 13 or whatever*/

/*lets say i am going to set code to run automatically on daily bases at x time*/

/*so first i am going read file in*/

data class;

set sashelp.class;

run;

/*then i am going pull distinct age from class table*/

proc sql;

create table dist_age as select distinct Age from class;

run;

/*so for today's case, dist_age table looks like this*/

Age

/*now i want to send email to team saying that*/

Hi Team, we receive class file today which has 6 distinct age which are: 11,12,13, 14, 15, 16.

/*but tomorrow message would be different*/

Hi Team, we receive class file today which has 2 distinct age which are: 21, 22.

/*so basically i am looking for one macro variable which i can write in message like*/

filename mymail email;

subject="..............";

to=".....";

data _null_;

set datasetname;

file mymail;

put "Hi Team, we receive class file today which has &n distinct age which are: &xyz_macrovariable";

/*put Hi Team, we receive class file today which has 6 distinct age which are: 11,12,13, 14, 15, 16*/

run;

Thank you so much for staying with me Tom...!!!

Re: creating macro variable for multiple value

Tom — Tue, 07 Oct 2014 02:32:58 GMT

IF you want to create A macro variable with multiple values then the code I posted using PROC SQL with the INTO and SEPARATED BY clauses is the easiest way. You can use the automatic macro variable SQLOBS to find the N.

IF you want to produce a REPORT (or an EMAIL message) with multiple values then the code I posted using a DATASET is the easiest way since you will not need to use macro variables at all.

Re: creating macro variable for multiple value

woo — Tue, 07 Oct 2014 03:17:13 GMT

got it. thanks a lot...Tom...lot to learn from your code...glad we have u on community...