BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
viviwu
Calcite | Level 5

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
1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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

10 REPLIES 10
Tom
Super User Tom
Super User

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
Calcite | Level 5

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
Super User Tom
Super User

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
Calcite | Level 5

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
Super User Tom
Super User

@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
Calcite | Level 5

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

 

 

andreas_lds
Jade | Level 19
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
Calcite | Level 5

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
Super User Tom
Super User

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
Calcite | Level 5

"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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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