cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Jeff_DOC
‎12-05-2025
  • 176 Posts
  • 40 Likes Given
  • 0 Solutions
  • 26 Likes Received

Re: Find Same Day in Alternating Weeks

by Pyrite | Level 9 in SAS Programming
‎02-12-2022 12:03 PM
‎02-12-2022 12:03 PM
Hi Astounding.   Thanks very much for taking the time to reply.   Yes, the value supplied by the %LET will always be a Monday. I Temp_Date will always be the run date and will change to the current date each time it runs. Since temp_date isn't static, and even if it was, I can't check for plus 7 or plus 14 since on the fourth run it would be plus 28. I know it could probably be better structured but this is what I got. Do you think there's a simple way to check to see if the week number of the year, based on the run date, is even or odd? ... View more

Re: Find Same Day in Alternating Weeks

by Pyrite | Level 9 in SAS Programming
‎02-11-2022 11:02 AM
‎02-11-2022 11:02 AM
Good morning.   Thanks to both of you for taking the time to respond. I probably did not explain some of this very well so here I go again.    I don't think it matters what the data (have) looks like since I'm only looking to set a YES/NO switch based on what day it is today (or the run date). The code that executes based on the YES/NO will execute just fine if the switch is set to YES (then execute the code). If today is not Monday in two-week intervals then do not execute the code. I'm not looking at the data to find records where a date field matches a particular date. I'm looking to conditionally execute code based on the params logic all using the week number of the year using run_date.   What I was hoping to do, using the mod function, was to determine if the return value was a whole number and if so execute the code. If there was a residual (anything after the decimal) then do not execute. So, given the week number of the year, for instance week 6 divided by 2 would leave me with 3-a whole number so the code should execute. However, week 7 divided by 2 would leave me with 3.5-not a whole number and so the code should not execute.   This was the starting point and there are probably way better ways to do it but I thought this might be an easy way to try to do it. ... View more

Find Same Day in Alternating Weeks

by Pyrite | Level 9 in SAS Programming
‎02-10-2022 07:33 PM
‎02-10-2022 07:33 PM
Good afternoon.   I am looking for a way to find the same Monday each week but identify those Mondays on every other week. For instance using the code below, if January 3, 2022 is a RUN_REPORT = 'YES', then January 10, 2022 should be a RUN_REPORT = 'NO' and January 17, 2022 should be back to RUN_REPORT = 'YES'.   I attempted to use the MOD function but I'm not familiar with it enough to know how to use it correctly.   What I'm apparently doing is to set the RUN_REPORT to 'YES' for two complete weeks and then to 'NO' for two more weeks.   Temp_date will be reassigned as the run date (today()) when testing is completed so the January dates in the example are just for test purposes to attempt to locate where I'm going wrong.   I'd really appreciate any help someone could give me.   Thank you.   %let temp_date = '03jan2022'd; /*%let temp_date = '10jan2022'd;*/ /*%let temp_date = '17jan2022'd;*/ data params; format week_number_start week_number_end mmddyy10.; week_number = week(&temp_date.); week_number_start = intnx('week',&temp_date.,-2,'beg'); week_number_end = intnx('week',&temp_date.,-1,'end') +1; if mod(week(&temp_date.),3) > 0 then do; run_report = 'YES'; end; else do; run_report = 'NO'; end; test = mod(week(&temp_date.),3); run; ... View more

Re: Proc Tabulate: Adding lookup values to ALL totals

by Pyrite | Level 9 in SAS Programming
‎12-27-2021 01:24 PM
‎12-27-2021 01:24 PM
Oh. And thank you very much for the "code block" suggestion. I'll use that in the future. ... View more

Re: Proc Tabulate: Adding lookup values to ALL totals

by Pyrite | Level 9 in SAS Programming
‎12-27-2021 01:21 PM
2 Likes
‎12-27-2021 01:21 PM
2 Likes
Hi Reeza.   Thank you so much for your time and efforts. I found a different approach to the solution. I think yours was probably more efficient and probably more elegant but I went with what I could make work. My solution was to create the proc formats (as you saw) and then simply call them as formats at the end. I'm not sure why this worked but it did. You can see the f=earned_level. and f=less_assist. at the end and that seemed to create both the "lookup" columns I need. As I said, I'm not if this was the best solution but it seems to work and I guess that's a good thing.   Again, thanks very much for your help.     table id_number = 'ID ' * name /*ALL={label='Total Points 1'*/ /*s=[just=center]}*/, (work_date = 'Date' ALL='Total Points' f=earned_level. f=less_assist.) * (pointspaid = ' ') / misstext = ' ' style=[font_size=2] box = {label = ' '};   ... View more

Re: Proc Tabulate: Adding lookup values to ALL totals

by Pyrite | Level 9 in SAS Programming
‎12-21-2021 03:10 PM
‎12-21-2021 03:10 PM
Hmmm. I have Run, Cancel, Save, Save As, Cut, Copy, Paste, Undo, Redo...That would make Paste my 7th icon and the 8th would be Undo? I tried using the Copy button but it removes everything just like a straight Paste. ... View more

Re: Proc Tabulate: Adding lookup values to ALL totals

by Pyrite | Level 9 in SAS Programming
‎12-21-2021 03:01 PM
1 Like
‎12-21-2021 03:01 PM
1 Like
When I paste in my code the formatting and indenting is removed. ... View more

Re: Proc Tabulate: Adding lookup values to ALL totals

by Pyrite | Level 9 in SAS Programming
‎12-21-2021 01:44 PM
‎12-21-2021 01:44 PM
Thank you very much for taking time to help. That works (sort of). It allows me to format the ALL column with the proc format. However, it replaces the total points column with the proc format values. I'd sure like to have them both (and the "less_value" actually from the proc format example). Sorry I did not include test data. I'll try to do that here in a text file.   Thank you. Here is the current proc tabulate example that is pretty close to what I need.   ods listing close; options missing = ' ' orientation= 'landscape' papersize='letter' topmargin= 1in bottommargin= 0.5in rightmargin= 0.5in leftmargin= 0.5in; ods Excel file= "<<PATH?>>\pay_project_&SYSDATE..xlsx" options(sheet_interval = 'none' sheet_name = 'Summary' frozen_headers = '2' orientation = 'portrait' center_horizontal = 'yes' suppress_bylines = 'yes' absolute_column_width = '10, 35, 15, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12'); proc tabulate data = met_10 style = [background=beige font_size=2.0] order = data; /* Class must agree with table although table can contain things class does not.*/ /* Classlev lets you set attributes for class variables since the class statement will not allow styles.*/ /* Multiple classlev statements are allowed. This way you can set different variables to different styles.*/ class id_number name work_date /*PositionPoints*/ / style=[font_size=2.0]; classlev work_date / style=[background=DMSWHITE /*DMSGRAY*/ font_size=2.0 cellwidth = 1000%]; var pointspaid; /*All sets totals for the columns. Misstext gives a blank for missing data instead of a zero.*/ table id_number = 'SID '*name ALL={label='Total' /*s=[just=center]*/}, (work_date = 'Date' ALL='Total Points') * (pointspaid = ' ' * f=4.0) / misstext = ' ' style=[font_size=2] box = {label = ' '}; /*This removes the redundant statistic identifier in the table.*/ /*Ex. this removes the word "Sum" from the table headers.*/ keylabel sum= ' ' N= ' '; title1 "TITLE 1"; title2 "Summary: Points Earned"; footnote1 "Source: &sysdate."; run; ods Excel close; ods listing; ... View more

Proc Tabulate: Adding lookup values to ALL totals

by Pyrite | Level 9 in SAS Programming
‎12-21-2021 11:59 AM
‎12-21-2021 11:59 AM
Good morning. I am having an issue with proc tabulate (at least I think that's my best option on how to proceed). The tabulate code I have works perfectly except I need to add two columns after the row total column. Both new columns need to be either a lookup value or a value from proc format and both are based on the total points per row. You can see from below that I have the proc tabulate written and it works great. I've also written the proc format just in case that's my best option. I'm just not sure how to put the proc format into play, or even if I can? The "HAVE" is missing two columns at the end. The "WANT" shows the two columns with the correct values.   Thank you very much. I'd sure appreciate any help or direction.   PS: The proc tabulate comments are for my benefit, they're not intended to explain this group how to use proc tabulate: I'm sure no one but me needs that. 🙂   proc format; value earned_level 0 - 15 = 'Not Enough Points' 16 - 75 = '$8.00' 76 - 79 = '$24.00' 80 - 85 = '$26.00' 86 - 106 = '$30.00' 107 - 127 = '$34.00' 128 - 148 = '$38.00' 149 - 169 = '$42.00' 170 - 190 = '$46.00' 191 - 211 = '$50.00' 212 - 232 = '$54.00' 233 - 253 = '$58.00' 254 - 274 = '$62.00' 275 - 295 = '$66.00' 296 - 316 = '$70.00' 317 - 337 = '$74.00' 338 - 358 = '$78.00' 359 - high= '$82.00'; value less_value 0 - 15 = 'Not Enough Points' 16 - 75 = '$87.60' 76 - 79 = '$22.80' 80 - 85 = '$24.70' 86 - 106 = '$28.50' 107 - 127 = '$32.50' 128 - 148 = '$36.10' 149 - 169 = '$39.90' 170 - 190 = '$43.70' 191 - 211 = '$47.50' 212 - 232 = '$51.30' 233 - 253 = '$55.10' 254 - 274 = '$58.90' 275 - 295 = '$62.70' 296 - 316 = '$66.50' 317 - 337 = '$70.30' 338 - 358 = '$74.10' 359 - high= '$77.90'; run;     ods listing close; options missing = ' ' orientation= 'landscape' papersize='letter' topmargin= 1in bottommargin= 0.5in rightmargin= 0.5in leftmargin= 0.5in; ods Excel file= "\\<<file_Path>>\pay_project_&SYSDATE..xlsx" options(sheet_interval = 'none' sheet_name = 'Summary' frozen_headers = '2' center_horizontal = 'yes' suppress_bylines = 'yes' absolute_column_width = '10, 30, 15, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12'); proc tabulate data = met_10 style = [background=beige font_size=2.0] order = data; /* Class must agree with table although table can contain things class does not.*/ /* Classlev lets you set attributes for class variables since the class statement will not allow styles.*/ /* Multiple classlev statements are allowed. This way you can set different variables to different styles.*/ class id_number name work_date PositionPoints / style=[font_size=2.0]; classlev work_date / style=[background=DMSWHITE /*DMSGRAY*/ font_size=2.0 cellwidth = 1000%]; classlev PositionPoints / style=[background=DMSWHITE /*DMSGRAY*/ font_size=2.0 textalign=center cellwidth = 1000%]; var points_paid; /*All sets totals for the columns. Misstext gives a blank for missing data instead of a zero.*/ table id_number = 'SID '*name*PositionPoints ALL={label='Total' /*s=[just=center]*/}, (work_date = 'Date' ALL='Total Points') * (points_paid = ' ') * f=4.0 / misstext = ' ' style=[font_size=2] box = {label = ' '}; /*This removes the redundant statistic identifier in the table.*/ /*Ex. this removes the word "Sum" from the table headers.*/ keylabel sum= ' ' N= ' '; title1 " TITLE 1"; title2 "Summary: Points Earned"; footnote1 "Source: Source File &sysdate."; run; ods Excel close; ods listing;   HAVE:   Date Total Points 01NOV2021 02NOV2021 03NOV2021 04NOV2021 05NOV2021 08NOV2021 09NOV2021 10NOV2021 11NOV2021 12NOV2021 15NOV2021 16NOV2021 17NOV2021 18NOV2021 19NOV2021 22NOV2021 23NOV2021 24NOV2021 25NOV2021 26NOV2021 29NOV2021 30NOV2021 SID Name Position Points 18 18 18 18 18 18 18 18 18 18 18 18 18 18 18 18 18 18 18 18 18 18 396 100 NAME 1 18 200 NAME 2 14 14 14 14 14 14 14 14 14 14 14 14 14 14 14                 196 16                             16 16 16 16 16 16 16 16 128 300 NAME 3 18 18 18 18 18 18 18 18 18 18 18 18 18 18 18 18 18 18 18 18 18 18 18 396 400 NAME 4 14 14                                           14 16   16 16 16 16 16 16 16 16 16 16 16 16 16 16 16 16 16 16 16 16 16 336 500 NAME 5 18 18 18 18 18 18 18 18 18 18 18 18 18 18 18 18 18 18 18 18 18 18 18 396 600 NAME 6 18 18 18 18 18 18 18 18 18 18 18 18 18 18 18 18 18 18 18 18 18 18 18 396 700 NAME 7 18 18 18 18 18 18 18 18 18 18 18 18 18 18 18 18 18 18 18 18 18 18 18 396 800 NAME 8 16 16 16 16 16 16 16 16 16 16 16 16 16 16 16 16 16 16 16 16 16 16 16 352 900 NAME 9 18 18 18 18 18 18 18 18 18 18 18 18 18 18 18 18 18 18 18 18 18 18 18 396 950 NAME 10 18 18 18 18 18 18 18 18 18 18 18 18 18 18 18 18 18 18 18 18 18 18 18 396 Total 936 952 934 934 934 934 934 934 912 912 948 948 948 948 966 966 966 966 950 950 950 990 21000   WANT:   Date Total Points     01NOV2021 02NOV2021 03NOV2021 04NOV2021 05NOV2021 08NOV2021 09NOV2021 10NOV2021 11NOV2021 12NOV2021 15NOV2021 16NOV2021 17NOV2021 18NOV2021 19NOV2021 22NOV2021 23NOV2021 24NOV2021 25NOV2021 26NOV2021 29NOV2021 30NOV2021 EARNED_LEVEL LESS VALUE SID Name Position Points 18 18 18 18 18 18 18 18 18 18 18 18 18 18 18 18 18 18 18 18 18 18 396     100 NAME 1 18 $82.00 $77.90 200 NAME 2 14 14 14 14 14 14 14 14 14 14 14 14 14 14 14                 196 $50.00 $47.50 16                             16 16 16 16 16 16 16 16 128 $38.00 $36.10 300 NAME 3 18 18 18 18 18 18 18 18 18 18 18 18 18 18 18 18 18 18 18 18 18 18 18 396 $82.00 $77.90 400 NAME 4 14 14                                           14 Not Enough Points Not Enough Points 16   16 16 16 16 16 16 16 16 16 16 16 16 16 16 16 16 16 16 16 16 16 336 $74.00 $70.30 500 NAME 5 18 18 18 18 18 18 18 18 18 18 18 18 18 18 18 18 18 18 18 18 18 18 18 396 $82.00 $77.90 600 NAME 6 18 18 18 18 18 18 18 18 18 18 18 18 18 18 18 18 18 18 18 18 18 18 18 396 $82.00 $77.90 700 NAME 7 18 18 18 18 18 18 18 18 18 18 18 18 18 18 18 18 18 18 18 18 18 18 18 396 $82.00 $77.90 800 NAME 8 16 16 16 16 16 16 16 16 16 16 16 16 16 16 16 16 16 16 16 16 16 16 16 352 $78.00 $74.10 900 NAME 9 18 18 18 18 18 18 18 18 18 18 18 18 18 18 18 18 18 18 18 18 18 18 18 396 $82.00 $77.90 950 NAME 10 18 18 18 18 18 18 18 18 18 18 18 18 18 18 18 18 18 18 18 18 18 18 18 396 $82.00 $77.90 Total 936 952 934 934 934 934 934 934 912 912 948 948 948 948 966 966 966 966 950 950 950 990 21000       ... View more

Determine Date Difference Between Dates Over Groups

by Pyrite | Level 9 in SAS Programming
‎12-02-2021 09:11 PM
‎12-02-2021 09:11 PM
Good evening. I hope there's someone who could give me a suggestion on how to solve this problem.   I have a dataset with many observations over separate groups. What I'm looking for is the difference between the first start date of the group and the ending date by the group. The twist is that the group is not limited to one grouping by variable. The groups are defined by five different variables. I only need the number of days on the final observation of each group but by all five by variables. What I need at the end of the day is one instance of each of all five variables with the number of days from the first date to the last date.   I know it sounds strange. Any alternative suggestions would be greatly appreciated.   Please see the attached Excel sheet for my (probably poor) attempt to explain this and below for my latest attempt to use  LAG. I've also tried RETAIN but can't seem to get either correct.   Thank you.   data attemp01; set XX; format lag_housing_start_date lag_housing_stop_date mmddyy10.; by var1 var2 var3 var4 var5 housing_start_date housing_stop_date; lag_var4 = lag(var4); lag_var5 = lag(var5); lag_housing_start_date = lag(housing_start_date); lag_housing_stop_date = lag(housing_stop_date); if (first.var1 and first.var2 and first.var3 and first.var4 and first.var5) then do; lag_var4 = var4; lag_var5 = var5; lag_housing_start_date = .; lag_housing_stop_date = .; end; else do; lag_var4 = var4; lag_var5 = var5; days_from_previous = housing_start_date - lag_housing_stop_date; end; run; ... View more

Re: "The workbook cannot be opened or repaired by Microsoft Excel beca...

by Pyrite | Level 9 in SAS Programming
‎11-01-2021 03:15 PM
‎11-01-2021 03:15 PM
Another great idea. Thank you Reeza. I will keep both in mind. ... View more

Re: "The workbook cannot be opened or repaired by Microsoft Excel beca...

by Pyrite | Level 9 in SAS Programming
‎11-01-2021 02:40 PM
‎11-01-2021 02:40 PM
Worked like a charm (after correcting all my typos). Thanks very much.     ... View more

Re: "The workbook cannot be opened or repaired by Microsoft Excel beca...

by Pyrite | Level 9 in SAS Programming
‎11-01-2021 02:12 PM
‎11-01-2021 02:12 PM
Well now that's interesting. Could you tell me how I would write a line(s) to the Excel file? I had no idea PUT only worked for the log.    There is an entire section above that code that will generate output for the same named file (if there is any that is). This section is only used if no output records exist. Could I use PROC EXPORT and write lines to the file? ... View more

"The workbook cannot be opened or repaired by Microsoft Excel because ...

by Pyrite | Level 9 in SAS Programming
‎11-01-2021 12:48 PM
‎11-01-2021 12:48 PM
Good morning.   I have code that run and periodically generates no qualifying records. When this happens I'd like the user to receive a report with the same name but with just two lines stating no qualifying records exist for this reporting period.    I have no idea what I'm doing wrong here.    Thanks very much.   %END; %ELSE %DO; data _null_; ods Excel file= "\\LOCATION\sasdata\Users\USERNAME\OCMS_No_PSC_&SYSDATE..xlsx"; put "No qualifying records were found for the report period"; put "SECTION NAME"; run; %END; ... View more

ODS Excel Grouping by Descending Variable

by Pyrite | Level 9 in SAS Programming
‎10-22-2021 04:28 PM
‎10-22-2021 04:28 PM
Good afternoon.   Thanks for any help someone could give me.    I have a dataset sorted descending by month/year. I am attempting to create a report with ODS Excel creating multiple tables on one worksheet. I would like the tables to be grouped by month/year and remain sorted descending. I don't want the user to have to scroll to the bottom to see the newest table. I attempted to use order and order = ?? but continue to get a message stating "Data set WORK.?? is not sorted in ascending sequence. The current BY group has RECEIVED_MONTH = SEP21 and the next BY group has RECEIVED_MONTH = AUG21. I would like to keep the data in descending order if possible. ODS also creates several other worksheets in the same workbook although the dataset this proc report depends on is only used for this work sheet.   Thank you very much.   ods Excel options(sheet_interval = 'output' sheet_name = '5+  Per Month' suppress_bylines = 'yes' orientation = 'landscape' pages_fitwidth = '1' pages_fitheight = '1' absolute_column_width = '11, 11, 11, 11, 11, 11, 11, 11, 11, 11, 11, 11, 20'); proc report data = report_wanted nowd style(REPORT)={cellpadding=5 background=#D0C7A8} style(COLUMN)={font_face=Arial font_size=1 background=white} style(HEADER)={font_face=Arial font_size=1 font_weight=bold foreground=black background=#EBE8DA};   column received_month var1 var2 var3 var4 count;   define received_month / 'Recieved/Month' group noprint order order = data; define location / 'var1'; define var2 / 'var2'; define var3 / 'var3'; define var4 / var4'; define count / 'Count';   by received_month; run; ... View more
Contact Me
Online Status
Offline
Date Last Visited
‎12-05-2025 12:46 PM
Likes given to
Likes from
Quick links