topic Re: CALL SYMPUT macro, can not return to the variable defined in SAS Programming

CALL SYMPUT macro, can not return to the variable defined

viviwu — Mon, 10 Jul 2017 22:15:07 GMT

Hello,

I have a qustion about the macro and CALL SYMPUT.

Here is my codes:

DATA test;
INPUT ID DATE MMDDYY8.;
DATALINES;
00001 05012017
00002 05032017
00003 05012017
00004 04282017
;
RUN;

%LET date_deb = DATE;
%LET date_fin = INTNX("month",&date_deb,+1,"sameday");

DATA _NULL_;
CALL SYMPUT("MOIS", SUBSTR(put(&date_deb,MMDDYY8.),7,2)!!SUBSTR(put(&date_deb,MMDDYY8.),1,2));
RUN;

DATA result;
SET test;
col1 = &date_deb;
col2 = &date_fin;
col3 = "Iwanttoprint&MOIS.it";
col4 = &MOIS;
col5 = SUBSTR(put(&date_deb,MMDDYY8.),7,2)!!SUBSTR(put(&date_deb,MMDDYY8.),1,2);
RUN;

Results:

DATE

20940

col1

20940

col2

20971

col3

Iwanttoprint . it

col4

col5

1705

20942

20973

Iwanttoprint . it

1705

20940

20971

Iwanttoprint . it

1705

20937

20967

Iwanttoprint . it

1704

My question is why col3 and col4 can not return to the value of 1705/1704? And could you please tell me how to fix it?

In order to debug, I tried to replace the %LET date_deb = 20940, the codes are as following:

%LET date_deb = 20940;
%LET date_fin = INTNX("month",&date_deb,+1,"sameday");

DATA _NULL_;
CALL SYMPUT("MOIS", SUBSTR(put(&date_deb,MMDDYY8.),7,2)!!SUBSTR(put(&date_deb,MMDDYY8.),1,2));
RUN;

DATA result2;
col1 = &date_deb;
col2 = &date_fin;
col3 = "Iwanttoprint&MOIS.it";
col4 = &MOIS;
col5 = SUBSTR(put(20940,MMDDYY8.),7,2)!!SUBSTR(put(20940,MMDDYY8.),1,2);
RUN;

Result:

col1

20940

col2

20971

col3

Iwanttoprint1705it

col4

1705

col5

1705

And it seems work in this way, but I don't understand why.

Thank you in advance.

VIVIAN

Re: CALL SYMPUT macro, can not return to the variable defined

Tom — Tue, 11 Jul 2017 00:13:02 GMT

Your macro variable MOIS is missing because you did not give it any date to use.

1531  %LET date_deb = DATE;
1532  %LET date_fin = INTNX("month",&date_deb,+1,"sameday");
1533
1534  DATA _NULL_;
1535  CALL SYMPUT("MOIS", SUBSTR(put(&date_deb,MMDDYY8.),7,2)!!SUBSTR(put(&date_deb,MMDDYY8.),1,2));
1536  RUN;

NOTE: Variable DATE is uninitialized.

When you used similar code in the later data step you have used a SET statement to bring a dataset that actually had a DATE variable.

You are working way too hard. Let SAS generate the value in the form you need directly.

data want ;
  set test ;
  col1 = date;
  col2 = intnx('month',col1,1);
  col4 = put(col2,yymmn4.);
  format date col1 col2 yymmdd10. ;
run;

Obs    ID          DATE          col1          col2    col4

 1      1    2017-05-01    2017-05-01    2017-06-01    1706
 2      2    2017-05-03    2017-05-03    2017-06-01    1706
 3      3    2017-05-01    2017-05-01    2017-06-01    1706
 4      4    2017-04-28    2017-04-28    2017-05-01    1705

Re: CALL SYMPUT macro, can not return to the variable defined

viviwu — Tue, 11 Jul 2017 06:07:42 GMT

Thank you for your reply, Tom.

Actually, this is the simplified codes I made, in the real business occasion, I need to use the date_deb, date_fin and MOIS many times later, that's why I need to make it as MACRO.

What I want is to keep the following macro form, but make the DATA RESULT show all the columns correctly, espacially the col3 and col4. And I already used the SET TEST in the DATA RESULT STEP.

DATA test;
INPUT ID DATE MMDDYY8.;
DATALINES;
00001 05012017
00002 05032017
00003 05012017
00004 04282017
;
RUN;

%LET date_deb = DATE;
%LET date_fin = INTNX("month",&date_deb,+1,"sameday");

DATA _NULL_;
CALL SYMPUT("MOIS", SUBSTR(put(&date_deb,MMDDYY8.),7,2)!!SUBSTR(put(&date_deb,MMDDYY8.),1,2));
RUN;

DATA result;
SET test;
col1 = &date_deb;
col2 = &date_fin;
col3 = "Iwanttoprint&MOIS.it";
col4 = &MOIS;
col5 = SUBSTR(put(&date_deb,MMDDYY8.),7,2)!!SUBSTR(put(&date_deb,MMDDYY8.),1,2);
RUN;

I'm a new user of SAS, hope that I explan my need clearly.

Thank you so much for your kind help.

Vivian

Re: CALL SYMPUT macro, can not return to the variable defined

Tom — Tue, 11 Jul 2017 14:05:24 GMT

If you want to set MOIS to a date, what date do you want to use?

If you wanted to use the previous month based on the current date then you might use something like.

data _null_;
  call symputx('mois',put(intnx('month',date(),-1),yymmn4.));
run;

Or you could just use macro code.

%let mois=%sysfunc(intnx(month,%sysfunc(date()),-1),yymmn4);

Re: CALL SYMPUT macro, can not return to the variable defined

viviwu — Wed, 12 Jul 2017 07:58:18 GMT

Hello Tom,

I think this function returns to the current yymm (1706)

%let mois=%sysfunc(intnx(month,%sysfunc(date()),-1),yymmn4);

But in my case, the MOIS is the yymm of the Date I have in the TEST date set which is a list of different date row by row.

%let date_deb = Date;
%let date_fin = intnx("month",&date_deb,+1,"sameday");

In the previous project, they all use this code to extract the yymm from the date_deb, but that date_deb is a fix date like "01MAY2017"d. When I changed date_deb to the list of date, it didn't work anymore.

DATA _NULL_;
CALL SYMPUTX("MOIS",substr(put(&date_deb.,yymmddn8.),3,2)!!substr(put(&date_beb.,yymmddn8.),5,2));
run;

Goodday,

Vivian

Re: CALL SYMPUT macro, can not return to the variable defined

Tom — Wed, 12 Jul 2017 12:43:35 GMT

@viviwu wrote:

In the previous project, they all use this code to extract the yymm from the date_deb, but that date_deb is a fix date like "01MAY2017"d. When I changed date_deb to the list of date, it didn't work anymore.
DATA _NULL_;
CALL SYMPUTX("MOIS",substr(put(&date_deb.,yymmddn8.),3,2)!!substr(put(&date_beb.,yymmddn8.),5,2));
run;

I do not understand want you want. If you want to store in the macro variable the code that can generate a string in YYMM format from a date variable then you would want something like.

%let datevar=date ;
%let mois=put(&datevar,yymmn4.);
data want ;
  set have ;
  length newvar $4 ;
  newvar = &mois ;
run;

Re: CALL SYMPUT macro, can not return to the variable defined

viviwu — Wed, 19 Jul 2017 09:17:07 GMT

Hello Tom, sorry to reply you late.

I tried your codes, which works very well in this form

%let datevar=date ;
%let mois=put(&datevar,yymmn4.);
data want ;
  set have ;
  length newvar $4 ;
  newvar = &mois ;
run;

But when I want to concatenate with other string, it didn't work:

%let date_deb = Date;
%let date_fin = intnx("month",&date_deb,+1,"sameday");
%let mois=put(&date_deb,yymmn4.);


DATA test;
SET save.list_id;
datedeb = &date_deb;
datefin = &date_fin;
month = "iMainBoutique&MOIS.Id";
segment = "iSegment2010_&MOIS.";
RUN;

And the results are:

Shop	Client	Date	Month	ID	datedeb	datefin	segment
BERLIN	Client1	01MAY2017	.	4890	20940	20971	iSegment2010_put(Date,yymmn4.)
MILAN FEMME	Client2	01MAY2017	.	77314	20940	20971	iSegment2010_put(Date,yymmn4.)
LONDON SELFRIDGES WOMAN	Client3	01MAY2017	.	186475	20940	20971	iSegment2010_put(Date,yymmn4.)
PARIS MONTAIGNE	Client4	01MAY2017	.	424636	20940	20971	iSegment2010_put(Date,yymmn4.)

The variable month and segment is not correct. (the output of month and segment wanted are "iMainBoutique1705Id" and "iSegment2010_1705", which are the name of the variables in other tables)

And the log are as following:

23         DATA test;
24         SET save.list_id;
25         datedeb = &date_deb;
SYMBOLGEN:  Macro variable DATE_DEB resolves to Date
26         datefin = &date_fin;
SYMBOLGEN:  Macro variable DATE_FIN resolves to intnx("month",Date,+1,"sameday")
27         month = "iMainBoutique&MOIS.Id";
SYMBOLGEN:  Macro variable MOIS resolves to put(Date,yymmn4.)
28         segment = "iSegment2010_&MOIS.";
SYMBOLGEN:  Macro variable MOIS resolves to put(Date,yymmn4.)
29         RUN;

NOTE: Character values have been converted to numeric values at the places given by: (Line):(Column).
      27:2   
NOTE: Invalid numeric data, 'iMainBoutiqueput(Date,yymmn4.)Id' , at line 27 column 2.
Boutique=BERLIN Client=Client1 Date=01MAY2017 Month=. ID=4890 datedeb=20940 datefin=20971
segment=iSegment2010_put(Date,yymmn4.) _ERROR_=1 _N_=1
NOTE: Invalid numeric data, 'iMainBoutiqueput(Date,yymmn4.)Id' , at line 27 column 2.
Boutique=MILAN FEMME Client=Client2 Date=01MAY2017 Month=. ID=77314 datedeb=20940 datefin=20971
segment=iSegment2010_put(Date,yymmn4.) _ERROR_=1 _N_=2
NOTE: Invalid numeric data, 'iMainBoutiqueput(Date,yymmn4.)Id' , at line 27 column 2.
Boutique=LONDON SELFRIDGES WOMAN Client=Client3 Date=01MAY2017 Month=. ID=186475 datedeb=20940 datefin=20971
segment=iSegment2010_put(Date,yymmn4.) _ERROR_=1 _N_=3
NOTE: Invalid numeric data, 'iMainBoutiqueput(Date,yymmn4.)Id' , at line 27 column 2.
Boutique=PARIS MONTAIGNE Client=Client4 Date=01MAY2017 Month=. ID=424636 datedeb=20940 datefin=20971
segment=iSegment2010_put(Date,yymmn4.) _ERROR_=1 _N_=4

Re: CALL SYMPUT macro, can not return to the variable defined

andreas_lds — Wed, 19 Jul 2017 11:12:40 GMT

DATA test;
SET save.list_id;
datedeb = &date_deb;
datefin = &date_fin;
month = cats("iMainBoutique", &MOIS, "Id");
segment = cats("iSegment2010_", &MOIS);
RUN

Untested, &mois contains a datastep function, so do don't enclose it in quotes.

Re: CALL SYMPUT macro, can not return to the variable defined

viviwu — Wed, 19 Jul 2017 12:19:48 GMT

Hello Andreas, thank you for your reply. It works well in the way in the example. But when I put into the original code, it still didn't work. I'm sorry that I should have provided the original code at the beginning instead of creating a example code. Here is the original code:

%let date_deb = Date;
%let date_fin = intnx("month",&date_deb,+1,"sameday");
%let mois=put(&date_deb,yymmn4.);

PROC SQL;
CREATE TABLE Client_base AS 

SELECT
DISTINCT R0.ID
,B3.sDivision as Zone
,P2.ProductCategory as Category
,sum(S2.iQuantity) as Quantity
,sum(S2.fTotalPrice) as Turnover

FROM  save.list_id R0
INNER JOIN VALIBLA2.VA_NmsRecipient R1			ON      (R0.ID = R1.iRecipientId)
INNER JOIN VALIBLA2.VA_ClientSegmentChina R2		ON	(R0.ID = R2.iRecipientId)

INNER JOIN VALIBLA2.VA_MAINBOUTIQUE1YARCHIVES B2	ON	(B2.iRecipientId = R1.iRecipientId)
INNER JOIN VALIBLA2.VA_Boutique B3 			ON	(B2.iMainBoutique&MOIS.Id = B3.iBoutiqueId)

LEFT JOIN VALIBLA2.va_sale S1				ON	(R1.iRecipientId = S1.iSaleRecipientId AND &date_deb. <= datepart(S1.tsDate) <= &date_fin.)

WHERE R1.iLinked = 1
GROUP BY 1,3,2;
QUIT;

where the following code never work:

INNER JOIN VALIBLA2.VA_Boutique B3 			ON	(B2.iMainBoutique&MOIS.Id = B3.iBoutiqueId)

And the log

SYMBOLGEN:  Macro variable MOIS resolves to put(Date,yymmn4.)
ERROR 22-322: Syntax error, expecting one of the following: IN, NOTIN.  

ERROR 76-322: Syntax error, statement will be ignored.

Re: CALL SYMPUT macro, can not return to the variable defined

Tom — Wed, 19 Jul 2017 12:39:57 GMT

Of course it does not work when used that way. Just replace the macro variable reference &MOIS with the text that the macro variable has been defined to have. And you get this as the generated SAS code

B2.iMainBoutiqueput(Date,yymmn4.)Id = B3.iBoutiqueId

Variable names cannot contain characters like (.) so that should generate SAS error messages.

It looks like you are trying to use a different VARIABLE NAME in your query for each observation. SAS cannot do that.

If you are running this as SQL code the way to make decisions based on the data is to use CASE statement.

case 
when (put(Date,yymmn4.) = '1601') then B2.iMainBoutique1601Id 
when (put(Date,yymmn4.) = '1602') then B2.iMainBoutique1602Id 
end= B3.iBoutiqueId

Re: CALL SYMPUT macro, can not return to the variable defined

viviwu — Wed, 19 Jul 2017 13:04:16 GMT

"It looks like you are trying to use a different VARIABLE NAME in your query for each observation. SAS cannot do that."

OK, at least I know that it is not possible in SAS 🙂 Thank you for your kind help Tom.

Good day,

Vivian