About sameer112217

sameer112217 · ‎08-10-2017

RW9 that is not the case. There are 6 tables with different fields and this table has transaction details only with no time point just month spending. It is joined with other tables. It has only monthly spending with numbers as ext like spend_number_1211 which is for jul 2017 month. I think 1179 is jan 2014. It does not mention month name just numbers as extension renamed as monthyear in label. How can i automate these month fields according to reporting month which will select in prompt.

sameer112217 · ‎08-10-2017

This one is slightly tricky There are variables in the source data table say sample1 which is like this Spend_number_1179 Spend_number_1180 Spend_number_1181 Spend_number_1182 Spend_number_1183 ..... .... Spend_number_1211 These variables are renamed in another table say sample2 like this which has monthly spending Spend_number_1211 is renamed in the label as "Monthly Spend for Jul 2017" Spend_number_1210 is renamed in the label as "Monthly Spend for Jun 2017" Spend_number_1209 is renamed in the label as "Monthly Spend for May 2017" Spend_number_1208 is renamed in the label as "Monthly Spend for Apr 2017" Spend_number_1207 is renamed in the label as "Monthly Spend for Mar 2017" Suppose if I automated for every reporting month. I take reporting month as July 30, 2017 it should take variable till Monthly Spend for Jul 2017 (this is just label) the actual variable name is spend_number_1211. Even using intnx function which I used earlier for automation... how can i automate the variable selection till reporting month... say example jul31, 2017 or jun30,2017..the variables starts from jan 2014 but spend_number_1211 needs some connection with dates... Can I use proc format or other way.. please help of how can i use date reporting connection to those variables thanks!

sameer112217 · ‎08-10-2017

Thank you everyone. Each reply and solution was unique and high quality material

sameer112217 · ‎08-04-2017

Data is not for single calender year but for last 3 years. By the way all replies are high class and helpful and i tested each reply from every user gave me new insight

sameer112217 · ‎08-04-2017

It would be great you can write the code as i never used lag statement

sameer112217 · ‎08-03-2017

Sample dataset data account; account transactionamount month FORD 100 JAN FORD 100 FEB FORD 100 MAR FORD . APR FORD . MAY FORD . JUN FORD 100 JUL FORD 100 AUG FORD 100 SEP FORD 100 OCT FORD 100 NOV FORD 100 DEC TOY 100 JAN TOY 100 FEB TOY 100 MAR TOY 100 APR TOY 100 MAY TOY 100 JUN TOY 100 JUL TOY 100 AUG TOY 100 SEP TOY 100 OCT TOY 100 NOV TOY 100 DEC I cannot officialy post work dataset but something like this... suppose if a customer has not made transaction for three consecutive months it needs to identified and in new column remark should mention.... broken can someone help me with code. thanks everyone

sameer112217 · ‎08-03-2017

Hello Suppose I have three variables... account no.... month and transaction amount how can i identify if the person has not made any transaction for more than three months account no i am aware of the formula max(of jan2016-dec2016) eq 0 then broken.. that is one month something like this ... if it finds 0 then give 1 values and increment it by 1 and if it is more than 3 then broken

sameer112217 · ‎07-26-2017

Hello! Can someone please help me I have created a program where I have automated dates till reporting month. Reporting month is one month prior to the current month. Suppose current month is July I have used %do i=1 %to &month ; %let date2=%sysfunc(intnx(month, %sysfunc(today()),-&i.,e), date9.);"&date2"n %end; month macro variable i have declared till previous month which is coming 6 and used the do loop which is fetching me right results from jan to june this year. The issue is in certain case if the user wants to have reporting month as May or april, how do I do that? this macro variable and macro is used at more than 100 places i can't go and change this everytime...I need some function or macro variable where it should take today() as previous month date or 2 months date prior to current month ... is it possible to define today() to previous month in macro variable.. That will solve my concern.

sameer112217 · ‎07-05-2017

Hello RW9 Thanks for the input. Transpose in my case wont work. I understand naming variable as "date"n is a bad idea but the client requirement is that. Transpose wont solve my concern. Is there any way I can use the &date macro variable and generate "date9"n values in the same loop in the rename variable in PROC SQL... It is not necessary that it should be in the same PROC SQL but it can be in date step but upto reporting month which is one month prior to current month...

sameer112217 · ‎07-05-2017

Hello All, I need urgent help for one of my task where I am automating the dates variable. &Year1 macro variable I have already defined it in data null statement earlier which is taking current year which is 2017 The issue is here in invoking with month Proc SQL; Select Var1, Var2, Var3, Var4 %do i=2 %to &monthmax +1; %let date=%sysfunc(intnx(month, %sysfunc(today()), -&i.), monname3.); (SUM(&date._&year1._Actuals))FORMAT=BEST12. AS &date&year1 , %end; Variable 6 from table ; quit; The issue is for the 5th variable which is &date&year1. It will create in the loop like this (Sum(Jan_2017_Actuals)) as JAN2017, (Sum(FEB_2017_Actuals)) as FEB2017, (Sum(MAR_2017_Actuals)) as MAR2017, (Sum(APR_2017_Actuals)) as APR2017, (Sum(MAY_2017_Actuals)) as MAY2017, So the variables names are jan_2017_actual, feb_2017_actuals etc and it is getting renamed as JAN2017, FEB2017 but my requirement is different. I need to rename it as "31JAN2017"n, "28FEB2017"n, "31MAR2017"n using the same macro variable &date. Is it possible in the same PROC SQL Step? It would be fine if I can later rename it in a seperate data step also as "31JAN2017"n instead of JAN2017 but there has to be loop where it should be upto the reporting month which is one month prior to the current month like above macro I used. Your help would be appreciated. I need exact code.

sameer112217 · ‎07-03-2017

I am interesting in learning high quality of sas macro writting. I am sas programer with 5 years of experience, but still I need to explore high level of macro programing. Can anyone suggest online free material Sameer

sameer112217 · ‎07-02-2017

Thank you very much everyone for your valuable contributions. Such high class knowledge sharing I am in love with this forum. Thanks Tom for providing the closest answer

sameer112217 · ‎07-01-2017

Tom thanks for the detailed code. That was very high quality stuff. I got it right all the code except for the SQL part which is throwing error... how can i eliminate the error in PROC SQL step. In the SQL there is issue on invoking it. 264 %put %datelist; ,"31JAN2017"n ,"28FEB2017"n ,"31MAR2017"n ,"30APR2017"n ,"31MAY2017"n ,"30JUN2017"n 265 Proc SQL; --- 13 266 Select age, ------ 180 ERROR 13-12: Unrecognized SAS option name, SQL. ERROR 180-322: Statement is not valid or it is used out of proper order. 267 %datelist() 268 from sashelp.class; 269 quit; 270 Proc SQL; 271 Select age, 272 %datelist() NOTE 137-205: Line generated by the invoked macro "DATELIST". 1 ,"&date"n - 22 ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string, a numeric constant, a datetime constant, a missing value, *, BTRIM, INPUT, LOWER, PUT, SUBSTRING, UPPER, USER. 273 from sashelp.class; 274 quit; NOTE: The SAS System stopped processing this step because of errors. NOTE: PROCEDURE SQL used (Total process time): real time 0.01 seconds cpu time 0.00 seconds

sameer112217 · ‎07-01-2017

Reeza the master tables have other character variables. But I need these month variable like 31JAN2017 to reporting month which will have amount for that particular month. If reporting month is July I need the variables till 30JUN2017 to be automatically added after those already present variables. Even if you show me you can remove those with keep and drop i will be fine but there should be no physical intervention of adding manually everytime. The reason is it is appearing in more than 100 places in different jobs. I need to automated. If it can be done with data step and not by proc sql i am fine but i need exact sas code please. Thanks

sameer112217 · ‎07-01-2017

Thanks for the detail step but it did not work out because first do look statement is 0 to 11 and other is month function which is not number it is throwing error. Also i need comma after months in variable. thanks anyways for effort.

Online Status	Offline
Date Last Visited	‎06-25-2023 08:13 AM

Excel cell in SAS Excel Add in as input parameter to infomap table

Proc Report - Email Size

How do i Transpose column into Rows?

Inner Join based on word in two variables

Transpose Column into rows

Re: extracting SAS word from string with special characters

Re: extracting SAS word from string with special characters

extracting SAS word from string with special characters

Re: Proc Report Width

Re: Proc Report Width

Re: How do i Transpose column into Rows?

Re: Transpose Column into rows

Re: Transpose Column into rows

Re: extracting SAS word from string with special characters

Re: SAS DI deployed and schedule jobs vs manual- diff results

Adding dummy data to the dataset

Re: Formula calculation

Re: Automating the variables according to reporting month

Automating the variables according to reporting month

Re: Counting three consecutive month with no transaction

Re: Counting three consecutive month with no transaction

Re: Counting three consecutive month with no transaction

Re: Counting three consecutive month with no transaction

Counting three consecutive month with no transaction

SAS changing today() to previous month and subsequent

Re: Date macro variable char to number

Date macro variable char to number

SAS Macro learning

Re: date macro variables

Re: date macro variables

Re: date macro variables

Re: date macro variables