About cbrotz

cbrotz · ‎10-06-2017

Is it possible to use the Contains statement together with a macro list with more than one lookup? I am trying do multiple lookups using partial strings. It works if just one selection is made but not more than one....If I just send through "ASSORTED" for example, I get all Mat_Desc that contain the word "Assorted". I would like to do a lookup on all that are i the list....I tried the LIKE statement and was not having much luck with that either.... PROC SQL; 38 CREATE TABLE WORK.MATERIALS_4 AS 39 SELECT t1. * 40 41 FROM BIRD.V_MATERIALS t1 42 where t1.MAT_DESC CONTAINS (&value_list6); NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements. NOTE: Line generated by the macro variable "VALUE_LIST6". 42 "ASSORTED","POSTERS" _ 22 76 ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, ), *, **, +, -, /, <, <=, <>, =, >, >=, ?, AND, BETWEEN, CONTAINS, EQ, EQT, GE, GET, GT, GTT, IN, IS, LE, LET, LIKE, LT, LTT, NE, NET, NOT, NOTIN, OR, ^, ^=, |, ||, ~, ~=. ERROR 76-322: Syntax error, statement will be ignored. Thank you!

cbrotz · ‎10-04-2017

So I think I am seeing the issue as I have run these macros a few more times......I just want to be clear what is happening. I made a few selections for three variables Level2 Level3 (both character) and Material which is numeric. %LET LEVEL20 = 2; 11 %LET LEVEL21 = POSTERS; 12 %LET LEVEL22 = PLACARDS; 13 %LET LEVEL2_count = 2; 14 %LET LEVEL2 = POSTERS; 15 %LET LEVEL3_count = 1; 16 %LET LEVEL3 = MAINT LABELS; 17 %LET Material_count = 1; 18 %LET Material = 5784; So for Level2 the results are 2, POSTERS, PLACARDS Level3 the results are 1, MAINT LABELS Material the results are 1, 5784 In this particular situation, I am comparing these results in one more step using a left join back to a master list. The Level2 value 2 will drop because 2 is not a valid value. Level3 1 will drop because it is also not valid. I see an issue with my data in the Material example because 1 is valid and I am getting data that was not asked for. I could have them type in a Material Description instead and that would eliminate the numeric issues....if i am following this correctly? This feature could be very useful....I was hoping to get the numeric piece to work as well and will play around with this some more. Thanks again.

cbrotz · ‎10-03-2017

Ok I am sure you are right but it is working okay so far. Are you saying if one selection exceeds 200 characters or in total? Also, as long as I am the one naming the macros, and don't overlap, etc. wouldn't that control the pattern? Yes taking the quotes out worked - thanks. I saw the link you had to a more complicated solution. I will take a look at that and see if I can follow it.

cbrotz · ‎10-03-2017

One more quick question on this code....should it work the same for numeric values? I can get the value list but the where t1.level2 in (&value_list); is looking for character data or something and I keep getting an error when I use numeric on a material code: proc sql noprint; select distinct cats('"',value,'"') into :value_list2 separated by ',' from dictionary.macros where name like 'MATERIAL%'; quit; %LET Material1 = 5784; 24 %LET Material2 = 8001108; 25 %LET Material3 = 409400; where t1.MATERIAL IN (&value_list2); Thanks!

cbrotz · ‎09-29-2017

Worked perfectly! Thanks

cbrotz · ‎09-29-2017

The values come from a SAS EG Prompt routine....I attached a screenshot. The values in the prompt come from another table. I think it has a routine that just assigns the names based on the root name in the prompt from the looks of it.

cbrotz · ‎09-29-2017

I have code that uses input from an SAS EG prompt. There can be multiple values - over 100 that could be selected. I am looking for a way to code for whatever number come thorugh. The code below allows me to use three. I am hoping to not have to code for all 100+. This is the log: %LET _SASPROGRAMFILEHOST=''; 10 %LET LEVEL20 = 4; 11 %LET LEVEL21 = PLACARDS; 12 %LET LEVEL22 = POSTERS; 13 %LET LEVEL23 = NEWSLETTER BINDERS; I am currently using these variables like this to select in my SQL code: WHERE t1.LEVEL2 IN ("&LEVEL21","&LEVEL22","&LEVEL23"); How do I code for any amount that may come through? Thanks

cbrotz · ‎07-11-2017

I need code that will count correctly. Neither of the code examples - either the array or the proc expand are giving the correct totals when a month is skipped. In the data example, if the 11 months are added together accounting for the skipped month, it should total 2032.45. So far, I do not see a code example that is performing this calculation correctly.

cbrotz · ‎07-11-2017

Is that what I need to use Proc Expand? I think we must have it because I tried running proc expand a few different ways but am not getting the right output yet. There are just so many options I need to read through all of them. This is the code I am trying to modify. Every time I use from=month, I get disconnected from the server.... proc expand data=work.fix_the_date /* input dataset */ out=work.add_roll_12; /* output dataset */ /* input timeframe - monthly */ *align=end; /* alignment of timeframe - end of month */ ; by group_status fcst_prod_grp ; * each time series is grouped; id sasdate; * specify time series variable; convert sum_of_billings / method=none; * do not interpolate missing values; * calculate 3-period moving sum without missing values, and set the first 2 values at ; * beginning of each by-group to missing; convert sum_of_billings=calc_roll3 / method=none transformout=(nomiss movsum 3); run;

cbrotz · ‎07-11-2017

Posts: 27 Re: Creating a Rolling 12 Month Total 3m ago I am currently working with the following code. Does anyone know how to modify to account for missing months? We use SAS EG 7.13 data ret_avg12; set work.summary_ret_data; by group_status fcst_prod_grp yyyymm; array values(12) _temporary_; if first.fcst_prod_grp then do; call missing(of values(*)); count=0; roll_tot=0; end; count+1; roll_tot+sum_of_billings; values(mod(count, 12)+1) = sum_of_billings; if count>=12 then roll_tot=sum(of values(*)); format roll_tot dollar12.2; run; In the following data for example if a month is missing, roll_tot needs to calculate based on 11 months....or whatever span of time is missing. Thank you! group_st fcst_pr yyyymm sum_of_bi count roll_tot 3000011 DQFS 201301 354.4000 1 $354.40 3000011 DQFS 201302 94.0000 2 $448.40 3000011 DQFS 201303 48.0300 3 $496.43 3000011 DQFS 201304 119.0000 4 $615.43 3000011 DQFS 201305 261.9400 5 $877.37 3000011 DQFS 201306 359.2200 6 $1,236.59 3000011 DQFS 201307 146.0000 7 $1,382.59 3000011 DQFS 201308 198.4500 8 $1,581.04 3000011 DQFS 201309 143.2500 9 $1,724.29 3000011 DQFS 201310 65.2700 10 $1,789.56 3000011 DQFS 201311 292.7000 11 $2,082.26 3000011 DQFS 201312 71.0000 12 $2,153.26 3000011 DQFS 201401 170.2200 13 $1,969.08 3000011 DQFS 201403 205.4000 14 $2,080.48 should be 2032.45 3000011 DQFS 201404 652.5000 15 $2,684.95 should be 2565.95 3000011 DQFS 201405 18.5200 16 $2,584.47 should be 2322.53 3000011 DQFS 201406 64.2000 17 $2,386.73 should be 2027.51

cbrotz · ‎07-06-2017

Thank you!

cbrotz · ‎07-06-2017

Reeza - thank you for your patience - this is not how I usually code so I had to work through it a bit but I think this part is SOLVED. I would tag you but I do not see a way to do that. 🙂 Have a good evening.

cbrotz · ‎07-06-2017

So I made a couple more changes below....zeroed out a field I am calling roll_tot at each break and then adding "sum_of_billings to that field to accumulate. I changed "mean" to "sum". I am not sure how that is going to affect the overall results but they look good from what I am seeing. data ret_avg12; set work.summary_ret_data; by group_status fcst_prod_grp yyyymm; array values(12) _temporary_; if first.fcst_prod_grp then do; call missing(of values(*)); count=0; roll_tot=0; end; count+1; roll_tot+sum_of_billings; values(mod(count, 12)+1) = sum_of_billings; if count>=12 then roll_tot=sum(of values(*)); format roll_tot dollar12.2; run;

cbrotz · ‎07-06-2017

Ok so I inserted my billing total in place of the word "open" and the code works but the output I am getting does not break and give me a totals after group_status and fcst_prod_grp and I am not looking for an average but a rolling total at the breaks - it needs to reset.... Do you know what I need to do to get it to give rolling totals and break and reset after the by group? Again I am sorry I really am just copying what you have here. Perhaps once I get a working example I will understand it better. proc sort data=sashelp.stocks out=stocks; by stock date; run; data stocks_avg12; set stocks; by stock; array values(12) _temporary_; if first.stock then do; call missing(of values(*)); count=0; end; count+1; values(mod(count, 12)+1) = sum_of_billings; if count>=12 then average=mean(of values(*)); format average dollar12.2; run; proc expand data=stocks_avg12 out=stocks_avg12_method2; by stock; id date; convert open=average2 / transformout=(movave 12 trimleft 11); run;

cbrotz · ‎07-06-2017

This is the original code that I am using....I did not delete any comments. I do not know what "open" means. proc sort data=sashelp.stocks out=stocks; by stock date; run; data stocks_avg12; set stocks; by stock; array values(12) _temporary_; if first.stock then do; call missing(of values(*)); count=0; end; count+1; values(mod(count, 12)+1) = open; if count>=12 then average=mean(of values(*)); format average dollar12.2; run; proc expand data=stocks_avg12 out=stocks_avg12_method2; by stock; id date; convert open=average2 / transformout=(movave 12 trimleft 11); run;

Online Status	Offline
Date Last Visited	‎11-19-2025 12:41 PM

Re: How to do an Excel Refresh in Windows Task Scheduler while Unatten...

How to do an Excel Refresh in Windows Task Scheduler while Unattended ...

Re: Looking for how to code for an apostrophe in an IN: statement

Looking for how to code for an apostrophe in an IN: statement

Re: Automatically open output when running query in project in SAS EG ...

Re: LOST!! EG 7.1 to 8.3 - Append and Code features

Re: Creating a Numeric Prompt in SAS EG

Re: Creating a Numeric Prompt in SAS EG

Re: Creating a Numeric Prompt in SAS EG

Re: Creating a Numeric Prompt in SAS EG

Re: LOST!! EG 7.1 to 8.3 - Append and Code features

Re: Where statement in SAS Code and Usage

Re: Where statement in SAS Code and Usage

Re: Tips for Dealing with a Large Table Join and Run Time

Re: Syntax to Compare a Date from a Date Prompt to another Date Field

Using Contains Statement with a Macro in Proc SQL

Re: How to code for multiple macro variable values?

Re: How to code for multiple macro variable values?

Re: How to code for multiple macro variable values?

Re: How to code for multiple macro variable values?

Re: How to code for multiple macro variable values?

How to code for multiple macro variable values?

Re: Create 12 Month Rolling Total that Accounts for Missing Months

Re: Create 12 Month Rolling Total that Accounts for Missing Months

Create 12 Month Rolling Total that Accounts for Missing Months

Re: Creating a Rolling 12 Month Total

Re: Creating a Rolling 12 Month Total

Re: Creating a Rolling 12 Month Total

Re: Creating a Rolling 12 Month Total

Re: Creating a Rolling 12 Month Total