Hello, I am really struggling to get the first day of year in date9. format into a variable. Can someone please tell me what i am doing wrong here and maybe what would be best. Thanks my code %let date_range_min = %sysfunc(mdy(1,1,year(date()))); Log error ERROR: Required operator not found in expression: year(date()) ERROR: Argument 3 to function MDY referenced by the %SYSFUNC or %QSYSFUNC macro function is not a number. ERROR: Invalid arguments detected in %SYSCALL, %SYSFUNC, or %QSYSFUNC argument list.  Execution of %SYSCALL statement or %SYSFUNC        or %QSYSFUNC function reference is terminated. Thanks Damon ... View more

I have the following section of a proc sql.  The bold section below i am having trouble making it work.  I created a variable cmryear that contains a 4 digit year.  Depending on the date of the observation i may want to subtract one from the year for my cmrperiod variable.  Does anyone have a suggestion as to how i can do this.  I have tried put and input and just seem get errors when i do. I basicly want to subtract 1 from &cmryear variable for observations falling into the date range.  The problem is that It doesnt like the &cmr year because it is a character value so it has to be converted.. Thank you all for your time. %let cmr_period = "CN/KTZ 2016 CMR" %let cmryear= %sysfunc(compress(&cmr_period ,"/",A)); %put output of cmryear from log is  2016 proc sql, ---other stuff      Case             when "&date_range_min"d <= datepart(transaction_date) <= "&date_range_max"d then catx(" ",mat.customer, "&cmryear", "CMR")             when intnx("year","&date_range_min"d,-1,"sameday") <= datepart(transaction_date) < "&date_range_Min"d then catx(" ", &cmryear-1, "CMR")         end as CMRPeriod, ---other stuff quit; log error  - ERROR: Expression using subtraction :smileyminus: requires numeric types. ... View more

I have the following data and query data have;    informat date date9. quantity 2.;    input part $ date quantity;    format date date9. quantity 2.;    datalines; A 01jan2013 10 A 01JUN2013 5 A 12DEC2013 4 A 01FEB2014 6 A 15JUN2014 20 B 20FEB2013 5 B 15JAN2013 6 ; proc sql;     create table almostwant as     select part, year(date) as year, sum(quantity) as quantity         from have     group by part, year     ; quit; I would like to output this data so that each year is created as a new variable rather then in a list format.  So i would like to see the data look like below but i cant seem to get it to work.  Any suggestions would be greatly appreciated. Want: Part     2013qty      2014qty A               19          26 B               11          . ... View more

I have a macro variable that is in date9. format &max_date = 21may2014 I would like to change this format to mmddyy10. so it looks better in the title on the results page. Any suggestions on this? I have googled it but been unsuccessful in finding an answer. Thanks ... View more

I am struggling to find a way to look up 2 values in a table.  In access i would have used a dlookup function. I want to set macro 2 macro variables to give me the min and max date of a field.  Is there something comparible to dlookup in SAS? %let date_min = ? (min date of Record set date field) %let date_max = ? (Max date of Record set date field) ... View more

I am having a problem with %_eq_conditional_dropdps  code.  I use this in other programs and have had no issues.  I have one program now that errors out every time.  I cant figure out why SAS doesn't like it. Here is a copy of the log output.  Any thoughts on why this would be. 141    %_eq_conditional_dropds(temp_model_totals_by_apg,temp_actual_totals_by_apg,temp_YTD_Cost_V);        _        180 WARNING: Apparent invocation of macro _EQ_CONDITIONAL_DROPDS not resolved. ERROR 180-322: Statement is not valid or it is used out of proper order. Thanks you. ... View more

I have working format and proc tabulate outputting the information i want.  I would like to add trafic lighting to this.  Is there a way that i can add Red and Green formatting to the below proc format statement?  I need to maintain the number formats and have the trafic lighting affect.  I have seen many options to do either of these alone, but how can i get both formats. Thank you all very much. proc format;     picture thousands (round)     0-High = '000,000,009 K' (mult=.001 prefix='$')     LOW-0 = '000,000,009) K' (mult=.001 prefix='($'); Run; proc tabulate data=temp_YTD_Cost_V                 out=test;         var costv;     class customer gd apg1 usage_type;     table gd* apg1,             customer='' * (usage_type='' all) * costv=''*f=thousands. * sum='';     run; ... View more

     I am having some trouble with the following array data step.  With my limited understanding of Arrays and SAS programing I thought that i could use an arrays variable name to locate data into the array.  I cant seem to google anything that explains this very well.  In this data step I am trying to use the Usage_type that is equal to the array variable names to change the data from long to wide.  I get an error in the log for "Invalid numeric data, usage_type='AA'.  Do i have to call out the usage type as a character some how?   I know i could use proc transpose but i really need to start using arrays more.  Can anyone tell me why this doesn't work. Thank you so much. Data temp_ytd_cost_v;      array ytdcv{4} US OH AA RM;      do until (last.apg1);           set temp_ytd_cost_v;           by customer gd apg1;              ytdcv{usage_type}=costV      end; Run; data type: usage_type = char, same as variable names of the array ... View more