About Krueger

Krueger · ‎02-12-2020

The following has examples, with code on what I think you are trying to determine. https://documentation.sas.com/?docsetId=sqlproc&docsetTarget=p1nlqenp6yoyitn1tbz095cbvxt3.htm&docsetVersion=9.4&locale=en

Krueger · ‎02-07-2020

If you provide some example data and the log it would be helpful in troubleshooting what your issue is. Below I've provided an example of what I assume you are trying to do. You can try to make necessary corrections on your side from there. data have; input a b c d; datalines; 1 . . . . 3 . . . . 4 . . . . 2 ; run; data want; set have; test = ifn(not missing(a), a, ifn(not missing(b), b, ifn(not missing(c), c, ifn(not missing(d), d,0)))); run;

Krueger · ‎01-28-2020

This worked for the example data but the live data it didn't account for the different weekdays. If I added 7/4/2019 for example (Thursday) Item 2 only accounts for the 3 ID's still. I did implement a group by to this section which fixed that part but wasn't sure how to update Item 3 to accommodate the different grouping's so it would divide correctly. Adding a by statement(?) to item3 didn't seem to help although I'm sure the solution to this is simple. Thank you again though for your input I definitely learned a lot from this!

Krueger · ‎01-28-2020

This ended up working perfectly as it also accounts for different WeekDays besides wednesday. Thank you!

Krueger · ‎01-28-2020

Thanks for breaking this down and explaining it. I think this will work for what I'm after. Going to implement into live data before accepting as solution.

Krueger · ‎01-28-2020

Definitely on the right track. I need the average though but this gives me a place to experiment further with. Thanks!

Krueger · ‎01-28-2020

Thanks for the feedback I appreciate it. I'll try to do a better job explaining... Ultimately I'm trying to determine from 1/1/2019-12/31/2019 the average number of ID's by location and hour for a given WeekDay (Monday-Sunday). The final Dataset would look similar to this: Given we are dealing with Averages these (AVG column) will likely be 1.1056, 2.223 etc.. but again just trying to keep it simple. In the Have dataset each date (7/3/2019, 7/10/2019, etc..) are all Wednesdays. My actual dataset has days 1/1/2019-12/31/2019. I'm needing all of these dates grouped into the WeekDate format however so what I want is it grouped Monday-Sunday. So if I put in 1/2/2019 or even 12/25/2019 (both are Wednesday's) it would also apply to the above group. So from there... we have unique ID's for each location and on a timeframe from 8AM-8PM (in this example I kept it down to 8AM-12PM). ID A may only appear 2 times out of the potential 5 (in my example) hours for any particular Wednesday, but may appear 3 times another Wednesday; both of which are unique entries which will be counted towards the average. So back to my original set we can see ID C appears during ALL of the Wednesdays provided, but only appears for 8AM & 9AM. We know there are 3 Wednesdays being calculated: 7/3/2019, 7/10/2019, 7/17/2019. We know we have 3 ID's: A,B,C. For hours 8AM & 9AM users A, B & C have entries for each Wednesday. For Hours 10AM-12PM users A & B have entries for each Wednesday...ID C does not have entries so they are not counted. So we know on 7/3/2019, 7/10/2019 and 7/17/2019 there are 3 unique ID's for 8AM & 9AM and 9 total entries (3, 3, 3). However on the same dates above there are only 2 unique ID's for 10AM-12PM and only 6 entries (2, 2, 2). I want the average of these entries so we then know based off that information that the Average for Wednesday 8AM & 9AM = 3 and 10AM-12PM = 2. Now if we added another Wednesday with only 1 unique ID for 8AM there would be 4 unique ID's for 8AM and 10 total entries. At this point the average for Wednesday at 8AM would change from 3 to 2.5 (3, 3, 3, 1). Hopefully this makes more sense although I feel like I'm complicating it further in an attempt to explain it.

Krueger · ‎01-28-2020

I want to know the Average number of ID's that occur based on Location, WeekDay (Monday-Sunday) and Hour (8AM-12PM). I then want my output to look like my want dataset so that I have Location, WeekDay and Hour listed with all of the Averages as the last column. In my prior datastep I removed the duplicates and have it down to only distinct values by Location, WeekDay and Hour for each ID. So in the above test data there are 3 ID's... ID= C is the only ID that has only hours 8AM & 9AM ID's A & B have all hours listed. Averages for these would be 8AM = 3, 9AM = 3, 10AM-12PM = 2.

Krueger · ‎01-28-2020

I'm trying to figure out how to group the below data so that I can get the Average number of ID's for each time period based on the Week Week (Mon-Sun). The example below is only Wednesday as I was trying to keep this simple while testing out solutions. DATA Have; INPUT LOCATION ID $2. Day:MMDDYY9. Month:Date8. Hour:time.; FORMAT Day WeekDate3. Month Date8. Hour timeampm5.; DATALINES; 1 A 7/3/2019 1-Jul-19 08:00 1 A 7/3/2019 1-Jul-19 09:00 1 A 7/3/2019 1-Jul-19 10:00 1 A 7/3/2019 1-Jul-19 11:00 1 A 7/3/2019 1-Jul-19 12:00 1 A 7/10/2019 1-Jul-19 08:00 1 A 7/10/2019 1-Jul-19 09:00 1 A 7/10/2019 1-Jul-19 10:00 1 A 7/10/2019 1-Jul-19 11:00 1 A 7/10/2019 1-Jul-19 12:00 1 A 7/17/2019 1-Jul-19 08:00 1 A 7/17/2019 1-Jul-19 09:00 1 A 7/17/2019 1-Jul-19 10:00 1 A 7/17/2019 1-Jul-19 11:00 1 A 7/17/2019 1-Jul-19 12:00 1 B 7/3/2019 1-Jul-19 08:00 1 B 7/3/2019 1-Jul-19 09:00 1 B 7/3/2019 1-Jul-19 10:00 1 B 7/3/2019 1-Jul-19 11:00 1 B 7/3/2019 1-Jul-19 12:00 1 B 7/10/2019 1-Jul-19 08:00 1 B 7/10/2019 1-Jul-19 09:00 1 B 7/10/2019 1-Jul-19 10:00 1 B 7/10/2019 1-Jul-19 11:00 1 B 7/10/2019 1-Jul-19 12:00 1 B 7/17/2019 1-Jul-19 08:00 1 B 7/17/2019 1-Jul-19 09:00 1 B 7/17/2019 1-Jul-19 10:00 1 B 7/17/2019 1-Jul-19 11:00 1 B 7/17/2019 1-Jul-19 12:00 1 C 7/03/2019 1-Jul-19 08:00 1 C 7/03/2019 1-Jul-19 09:00 1 C 7/10/2019 1-Jul-19 08:00 1 C 7/10/2019 1-Jul-19 09:00 1 C 7/17/2019 1-Jul-19 08:00 1 C 7/17/2019 1-Jul-19 09:00 ; RUN; DATA WANT; INPUT LOCATION Day Hour:time. AVG; FORMAT hour timeampm5.; DATALINES; 1 WED 08:00 3 1 WED 09:00 3 1 WED 10:00 2 1 WED 11:00 2 1 WED 12:00 2 ; RUN; Below is one of my latest attempts which I think(?) will work (once I fix the error) but I'm not sure how to implement another step for this "fix". PROC REPORT DATA = have; 2167 title attempt; 2168 column location day hour id; 2169 define location / group; 2170 define day / group; 2171 define hour / group; 2172 define id / analysis mean; 2173 run; ERROR: id is an ANALYSIS variable but not numeric. NOTE: The SAS System stopped processing this step because of errors. NOTE: PROCEDURE REPORT used (Total process time): real time 0.00 seconds cpu time 0.00 seconds I understand ID is a character. What I don't know how to do is to group this to get an accurate SUM out of these or maybe I just need to convert it to numeric? I'm not sure. Any help would be appreciated.

Krueger · ‎01-22-2020

That makes sense thank you. I ended up doing the following to accomodate for the other entries as well (Brand & State) which in my example works, but needs testing on the live data now. Thanks! data diff; set have; by brand state key descending month; retain diff maxweeknum; if first.month then do; diff = ar-lag(ar); if first.brand or first.State or first.Key then diff =.; maxweeknum=weeknum; end; run;

Krueger · ‎01-22-2020

Hi all, what I'm tryin to do is calculate the AR difference between months and offset based on the weeknum. Below is my code which is working, however I need it to cut off and not calculate unless the Brand, State and Key all match from the previous record. Incorrect ARCalcs are output lines: 9-12, 17-20. The values for diff and arcalc should be '.' (the same as output lines 1-4). Hopefully this all makes sense. If not please ask away. data have; input Brand $ State $ Key AR commax8. drevmo:mmddyy10. week:mmddyy10. weeknum month; format drevmo mmddyy10. week mmddyy10.; datalines; ac ca 2 10000.00 01/01/2020 01/05/2020 1 1 ac ca 2 10000.00 01/01/2020 01/12/2020 2 1 ac ca 2 10000.00 01/01/2020 01/19/2020 3 1 ac ca 2 10000.00 01/01/2020 01/26/2020 4 1 ac ca 2 20000.00 02/01/2020 02/02/2020 1 2 ac ca 2 20000.00 02/01/2020 02/09/2020 2 2 ac ca 2 20000.00 02/01/2020 02/16/2020 3 2 ac ca 2 20000.00 02/01/2020 02/23/2020 4 2 ac ca 3 1000 01/01/2020 01/05/2020 1 1 ac ca 3 1000 01/01/2020 01/12/2020 2 1 ac ca 3 1000 01/01/2020 01/19/2020 3 1 ac ca 3 1000 01/01/2020 01/26/2020 4 1 ac ca 3 2000 02/01/2020 02/02/2020 1 2 ac ca 3 2000 02/01/2020 02/09/2020 2 2 ac ca 3 2000 02/01/2020 02/16/2020 3 2 ac ca 3 2000 02/01/2020 02/23/2020 4 2 bb ca 2 100 01/01/2020 01/05/2020 1 1 bb ca 2 100 01/01/2020 01/12/2020 2 1 bb ca 2 100 01/01/2020 01/19/2020 3 1 bb ca 2 100 01/01/2020 01/26/2020 4 1 bb ca 2 200 02/01/2020 02/02/2020 1 2 bb ca 2 200 02/01/2020 02/09/2020 2 2 bb ca 2 200 02/01/2020 02/16/2020 3 2 bb ca 2 200 02/01/2020 02/23/2020 4 2 ; run; proc sort data=have; by brand state key descending week ; run; data diff; set have; by brand state key descending month; retain diff maxweeknum; if first.month then do; diff = ar-lag(ar); maxweeknum=weeknum; end; run; data want; set diff; arcalc = ar - ((diff/maxweeknum)*weeknum); run; in the data diff section if I try filtering down further e.g. data diff; set have; by brand state key descending month; retain diff maxweeknum; if first.month then do; if brand=lag(brand) then do; diff = ar-lag(ar); maxweeknum=weeknum; end; end; run; It completely breaks the calculation and doesn't work as I am expecting it to.

Krueger · ‎01-16-2020

I suppose I don't understand how your step 2 is applying that logic as I don't see it. If you want counts > 1 change it from = to >.

Krueger · ‎01-16-2020

Is the only thing wrong with your Step2a dataset that it has the Cnt variable and your label's aren't set yet? If so just add your labels in that step, your additional columns and add their grouping after the brand name. Also get rid of your count(*) as Cnt from the select; it doesn't need to be included as the logic is being applied with your having clause. Edit: Like this, just compare results make sure they match. If not you might have to play with your group by. proc sql; create Table want as select HCPCS label='HCPCS Code', Brand_name, Generic_Name from Spend_util group by Brand_Name, HCPCS, Generic_Name having Count(distinct HCPCS) = 1; quit;

Krueger · ‎01-16-2020

I get a syntax error running as is(for the union all). If I add from table then union all works but at that point it's a completely different approach with just inserting the values into a table and then using the second part. Yeah I don't know SAS is weird to me... data list; input var $; datalines; 'AB' 'AE' 'AI' 'BE%' ; run; proc sql; create table old_method as select upcase(b.name), l.var from sashelp.baseball b join list l on upcase(b.name) like l.var; quit; If I swap l.var with 'BE%' it works just fine. Not sure what it's struggling with when I can do the exact same thing in SQL.

Krueger · ‎01-16-2020

EDIT: Looks like this doesn't work the same in SAS so you would have to update the logic below to work in SQL and then use SQL Pass through. I'm sure there are much better alternatives built-in to SAS however. There is no combined like and in statement for SQL (may be wrong regarding SAS). However what I would do if using SQL would be something like this. proc sql; create table list as select '%AB%' as var union all select '%AI%' as var union all select '%AE%' as var; quit; proc sql; create table old_method as select * from sashelp.baseball b join list l on upcase(b.name) like l.var; quit;

Online Status	Offline
Date Last Visited	‎07-08-2020 02:29 PM

Re: Hide/suppress CMD.exe prompts?

Hide/suppress CMD.exe prompts?

Re: Looking for resource's on creating heat maps.

Re: SAS lag and lead problem

Re: SAS help

Re: SAS help

Re: SAS help

Re: Converting date time to character

Re: SAS help

Re: Converting date time to character

Re: Hide/suppress CMD.exe prompts?

Re: Looking for resource's on creating heat maps.

Re: SAS lag and lead problem

Re: Looking for resource's on creating heat maps.

Re: Transposing and summing dataset help?

Re: Proc SGMAP - Can't limit bubble size in map bubble plot

Re: sum by group

Re: Add leading 0 to census tract value (character var)

Re: How to export an additional excel worksheet of text only

Re: What do these invalid data error notes mean?

Re: Duplicate issue? - Finding the difference in the duplicate IDs.

Re: Help!! SAS Enterprise advanced expression nested if-then statement

Re: Calculating Average while grouping data?

Re: Calculating Average while grouping data?

Re: Calculating Average while grouping data?

Re: Calculating Average while grouping data?

Re: Calculating Average while grouping data?

Re: Calculating Average while grouping data?

Calculating Average while grouping data?

Re: Cutting off datastep calculations

Cutting off datastep calculations

Re: Proc SQL Subquery

Re: Proc SQL Subquery

Re: move multiple 'or' statements into one statement

Re: move multiple 'or' statements into one statement