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 |
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
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
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
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);
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
@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;
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
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.
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.
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
"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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: