That's exatcly what I need! Thank you so much Tom! ... View more

Thank you KurtBremser, that's exactly what I want! ... View more

"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 ... View more

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. ... View more

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     ... View more

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 ... View more

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 ... View more