CALL SYMPUT macro, can not return to the variable defined

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 8
Accepted Solution

CALL SYMPUT macro, can not return to the variable defined

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

22209422094220973Iwanttoprint .  it.1705
33209402094020971Iwanttoprint .  it.1705
44209372093720967Iwanttoprint .  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

Accepted Solutions
Solution
‎07-19-2017 09:04 AM
Super User
Super User
Posts: 7,079

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

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

 

 

 

View solution in original post


All Replies
Super User
Super User
Posts: 7,079

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

[ Edited ]

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
Occasional Contributor
Posts: 8

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

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

Super User
Super User
Posts: 7,079

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

[ Edited ]

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

 

Occasional Contributor
Posts: 8

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

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

Super User
Super User
Posts: 7,079

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

[ Edited ]

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;
Occasional Contributor
Posts: 8

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

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:

ShopClientDateMonthIDdatedebdatefinsegment
BERLINClient101MAY2017.48902094020971iSegment2010_put(Date,yymmn4.)
MILAN FEMMEClient201MAY2017.773142094020971iSegment2010_put(Date,yymmn4.)
LONDON SELFRIDGES WOMANClient301MAY2017.1864752094020971iSegment2010_put(Date,yymmn4.)
PARIS MONTAIGNEClient401MAY2017.4246362094020971iSegment2010_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

 

 

Super Contributor
Posts: 345

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

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.

Occasional Contributor
Posts: 8

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

Posted in reply to andreas_lds

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.
Solution
‎07-19-2017 09:04 AM
Super User
Super User
Posts: 7,079

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

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

 

 

 

Occasional Contributor
Posts: 8

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

"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 Smiley Happy Thank you for your kind help Tom.

 

Good day,

Vivian

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 10 replies
  • 244 views
  • 0 likes
  • 3 in conversation