CALL SYMPUT macro, can not return to the variable defined

[viviwu]

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:

1

ID 1

DATE 20940

col1 20940

col2 20971

col3 Iwanttoprint .  it

col4 .

col5 1705

2

2

20942

20942

20973

Iwanttoprint .  it

.

1705

3

3

20940

20940

20971

Iwanttoprint .  it

.

1705

4

4

20937

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:

ID 1

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

[Tom]

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

[viviwu]

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

[Tom]

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);

[viviwu]

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

[Tom]

---

@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;

[viviwu]

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

[andreas_lds]

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.

[viviwu]

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.

[Tom · Accepted solution]

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

[viviwu]

"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