About cgates

cgates

Unfortunately nothing worked for me. I just gave up on automating the process for the protected information.

cgates · ‎04-03-2024

Thanks! This works except the by 200 element goes past the end date for the last date. In your example I would need it to stop at 31dec2023, but it goes to 18mar2024, since that is 200 days after 01sep2023.

cgates · ‎04-02-2024

Yes I know how to filter to between dates. That is what I would do if I had to pull by every 6 months or every year. I’m asking for a more condensed solution based on runtime. Or a loop that can take a starting and end date and loop through every 6 months without me having to hard code it.

cgates · ‎04-02-2024

I have a query that works fine, but it takes hours to run because of how much data it pulls in (like past 7 years of data). There is a new restriction being imposed stating that any queries on this database will be killed after 30 minutes of runtime - no exceptions. So now I have to find a way to still pull all of the data I need but in 30 minute (or less) increments. The most rudimentary way I can think to do this is to have separate queries for each year (with the hope that only pulling one year of data takes less than 30 min to run - this isnt guaranteed though so I might have to run every 6 month queries). EX. A query for 2020, another for 2021, another for 2022, and so forth. But I’m wondering if there’s a more eloquent/efficient way to write this with a loop that doesn’t require me to hard code the dates whether they’re every 6 months or every year. Basing it on runtime would be best since that is where the restriction is taking place. The hard part would be if I could get it to iterate based on time, the next run would have to know where the previous run stopped to it could append onto the previous dataset. Any help is appreciated! This is how the query is currently pulled: PROC SQL; CONNECT TO SQLSVR(DATABASE=DB, USER=MYID, PASSWORD=PASS READBUFF=1000); CREATE TABLE RECORDPULL AS SELECT * FROM CONNECTION TO SQLSVR (SELECT * FROM MYDATABASE WHERE ADD_DT <= ‘2023-12-31’); QUIT;

cgates · ‎01-31-2022

This is clunky but what I figured out on my own and works. data have; input person $ admin_date_1 : ?? mmddyy10. admin_date_2 : ??mmddyy10. admin_date_3 : ?? mmddyy10. admin_date_4 : ?? mmddyy10. admin_date_5 : ?? mmddyy10. admin_date_6 : ?? mmddyy10. type_1 $ type_2 $ type_3 $ type_4 $ type_5 $ type_6 $; format admin_date_1 mmddyy10. admin_date_2 mmddyy10. admin_date_3 mmddyy10. admin_date_4 mmddyy10. admin_date_5 mmddyy10. admin_date_6 mmddyy10.; datalines; JohnDoe 01/12/2021 01/13/2021 01/19/2021 01/30/2021 02/01/2021 02/15/2021 M P M P P J ; run; *in order for this to work correctly, you have to order the DAYS_BT_DOSE fields in ascending order like below in the format; %macro test; data want (keep=person new:); set have; format L_DAYS_BT_DOSE_1_2 L_DAYS_BT_DOSE_1_3 L_DAYS_BT_DOSE_2_3 L_DAYS_BT_DOSE_1_4 L_DAYS_BT_DOSE_2_4 L_DAYS_BT_DOSE_3_4 L_DAYS_BT_DOSE_1_5 L_DAYS_BT_DOSE_2_5 L_DAYS_BT_DOSE_3_5 L_DAYS_BT_DOSE_4_5 L_DAYS_BT_DOSE_1_6 L_DAYS_BT_DOSE_2_6 L_DAYS_BT_DOSE_3_6 L_DAYS_BT_DOSE_4_6 L_DAYS_BT_DOSE_5_6 F_DAYS_BT_DOSE_1_2 F_DAYS_BT_DOSE_1_3 F_DAYS_BT_DOSE_2_3 F_DAYS_BT_DOSE_1_4 F_DAYS_BT_DOSE_2_4 F_DAYS_BT_DOSE_3_4 F_DAYS_BT_DOSE_1_5 F_DAYS_BT_DOSE_2_5 F_DAYS_BT_DOSE_3_5 F_DAYS_BT_DOSE_4_5 F_DAYS_BT_DOSE_1_6 F_DAYS_BT_DOSE_2_6 F_DAYS_BT_DOSE_3_6 F_DAYS_BT_DOSE_4_6 F_DAYS_BT_DOSE_5_6 NEW_ADMIN_DT_1 NEW_ADMIN_DT_2 MMDDYY10. ; %do i = 1 %to 5; %let nxt = %eval(&i. + 1); %do b=&nxt %to 6; if TYPE_&i = 'P' and TYPE_&b = 'P' and intck('day',ADMIN_DATE_&i,ADMIN_DATE_&b) ge 17 then do; L_DAYS_BT_DOSE_&i._&b = ADMIN_DATE_&b; F_DAYS_BT_DOSE_&i._&b = ADMIN_DATE_&i; end; else if TYPE_&i in ('P','M','J') and TYPE_&b in ('P','M','J') and intck('day',ADMIN_DATE_&i,ADMIN_DATE_&b) ge 24 then do; L_DAYS_BT_DOSE_&i._&b = ADMIN_DATE_&b; F_DAYS_BT_DOSE_&i._&b = ADMIN_DATE_&i; end; %end; %end; NEW_ADMIN_DT_1 = COALESCE(of F_DAYS_BT_DOSE:); NEW_ADMIN_DT_2 = COALESCE(of L_DAYS_BT_DOSE:); run; %mend test; *execute macro; %test;

cgates · ‎01-31-2022

Sorry but that want dataset is not the one I described. The dates are also not correct in your want dataset. I figured out a solution myself that involves nested loops. Thank you for your assistance!

cgates · ‎01-28-2022

I have a wide dataset with 6 dates and 6 types. Each type number corresponds to the date number. I need to compare each date and type to each other (2 at a time) and find the earliest date that these requirements are met: Criteria If both types = P, the difference between the dates must be 17 or more. All other combinations must be 24 or more days apart I have to find the earliest date match. So in my example, my "have" dataset has all 6 entries. My want dataset has the earliest two dates that meet my criteria which are 2 & 4. I can calculate this manually but can't figure out how to do it in SAS. I would love to have this in some sort of iterative macro program instead of taking up hundreds of lines of code. My logic on paper 1. Calculate # of days between each admin_date (all permutations) 2. Ignore any that are not at least 17 days apart 3. Among those that are >= 17 and <24, check to see if both types are P. If so, take the combination with the earliest 2nd date. 4. If not, check those that are >= 24. Take the combination with the earliest 2nd date. My calculations for Day Differences Between: 1 & 2: 1 day (ignore, less than 17) 1 & 3: 7 days (ignore, less than 17) 1 & 4: 18 days (ignore, not both P) 1 & 5: 20 days (ignore, not both P) 1 & 6: 34 days (consider, ge 24) 2 & 3: 6 days (ignore, less than 17) 2 & 4: 17 days (consider, ge 17, both P) 2 & 5: 19 days (consider, ge 17, both P) 2 & 6: 33 days (consider, ge 24) and so forth Among all of the sets that met the criteria: 1 and 6, 2 and 4, 2 and 5, 2 and 6. 4 is the earliest so that's what I want. data have; input person $ admin_date1 : ?? mmddyy10. admin_date2 : ??mmddyy10. admin_date3 : ?? mmddyy10. admin_date4 : ?? mmddyy10. admin_date5 : ?? mmddyy10. admin_date6 : ?? mmddyy10. type1 $ type2 $ type3 $ type4 $ type5 $ type6 $; format admin_date1 mmddyy10. admin_date2 mmddyy10. admin_date3 mmddyy10. admin_date4 mmddyy10. admin_date5 mmddyy10. admin_date6 mmddyy10.; datalines; JohnDoe 01/12/2021 01/13/2021 01/19/2021 01/30/2021 02/01/2021 02/15/2021 M P M P P J ; run; data want; input person $ admin_date1 : ?? mmddyy10. admin_date2 : ??mmddyy10. type1 $ type2 $ ; format admin_date1 mmddyy10. admin_date2 mmddyy10. ; datalines; JohnDoe 01/13/2021 01/30/2021 P P ; run;

cgates · ‎09-02-2021

I'm testing out how to use hash objects in SAS 9.4 M6 to do fuzzy joins since PROC SQL just runs for hours on my larger dataset. I created some sample datasets (below) and what I want is for the merge to pull in exact matches on the "name" fields AND any matches that have a COMPLEV score less than 10. Right now, this code still only pulls in the exact matches. I'm very new to hash objects so I'm sure it's a simple fix but I've tried am in need of help. data A; infile datalines missover; length nameA $50; input nameA $ ; datalines; MICKEYMOUSE2000-01-02 DAFDUCK1990-09-23 GOOFYMAN1993-05-11 ; run; *second dataset with one exact match and two that differ slightly from those in dataset A; data B; infile datalines missover; length nameB $50; input nameB $ VDAY :ddmmyy10.; format VDAY ddmmyy10.; datalines; MICKEYMOUSE2000-01-01 07/08/2021 DAFFYDUCK1990-09-23 05/11/2021 GOOFYMAN1993-05-11 08/11/2021 ; run; *only pulling in exact matches, want it to pull in other fuzzy matches; data simplemerge ; if 0 then set work.B ; *load var properties into hash table; if _n_ = 1 then do; dcl hash B (dataset: 'work.B'); *declare the name B for hash using B dataset; B.definekey('nameB');*identify var in B to use as key; B.definedata('nameB','vday');*identify columns of data to bring in from B dataset; B.definedone();*complete hash table definition; end; set work.A; *bring in A data; if B.Find(KEY: nameA) ne 0 then do; if complev(nameA, nameB) < 10 then do; B.ref(key : nameB,data : nameB, data : vday); end; end; RUN;

cgates · ‎03-30-2021

You're right. The link I provided was just an example but it does work if I remove restrictions. The issue is the production link I need this for does need to be protected. Do you know of a way I can authenticate my Google credentials through SAS? I thought it would work because I was running the SAS program while logged into my Gmail but that doesn't seem to work.

cgates · ‎03-30-2021

I'm using SAS EG, 7.15 HF9 (7.100.5.6226) (64-bit) powered by SAS 9.4. If I paste this link into my browser, a .csv file is automatically downloaded to my PC: https://docs.google.com/spreadsheets/d/1xFgCLi7cr-_Z_hbhksYIv5jABk6EaBcPNWRlVzH5kxY/export?format=csv. This is a test document but the real data I need to download through SAS has sensitive data so I can't share that here. The code below works with the test document because there are no restrictions, but when I run the same code with the document that requires me to be signed in, I get HTML output instead of the csv data (copied below). Is there a way I can authenticate my Google credentials through SAS? filename in url "https://docs.google.com/spreadsheets/d/1xFgCLi7cr-_Z_hbhksYIv5jABk6EaBcPNWRlVzH5kxY/export?format=csv"; filename out "%sysfunc(pathname(work))\data.csv"; data _null_; infile in; input; if _n_> 1; file out; put _infile_; run; PROC IMPORT OUT= WORK.data DATAFILE= "%sysfunc(pathname(work))\data.csv" DBMS=CSV REPLACE; RUN; .csv snippet from SAS in HTML <!DOCTYPE html> <html lang="en"> <head> <meta charset="utf-8"> <meta content="width=300 <meta name="description" content="Create a new spreadsheet and edit with others at the same time -- from your computer <meta name="google-site-verification" content="LrdTUW9psUAMbh4Ia074-BPEVmcpBxF6Gwf0MSgQXZs"> <title>Google Sheets - create and edit spreadsheets online <style> @font-face { font-family: 'Open Sans'; font-style: normal; font-weight: 300; src: url(//fonts.gstatic.com/s/opensans/v15/mem5YaGs126MiZpBA-UN_r8OUuhs.ttf) format('truetype'); }

cgates · ‎12-28-2020

You're a godsend @RichardDeVen . Thank you so much! It worked perfectly.

cgates · ‎12-28-2020

Using SAS EG 7.15 HF9 (7.100.5.6226) (64-bit) on a Windows 10 computer. I have 12 variables that are each Yes/No. Each variable represents a job a person reported to have. They can report more than one job. What I need to do is loop through all 12 Y/N variables and determine which ones have "Yes" values. Then, for the "Yes" jobs I need to pull on each one's name and address fields and populate that in new variables for up to 3 jobs, two fields each: WORKNAME1, WORKADD1, WORKNAME2, WORKADD2, WORKNAME3,WORKADD3. To add more complexity since a person can report up to 12 jobs but we can only accept three, there is a hierarchy to determine which to bring in e.g. If they reported to have jobs in retail, government, healthcare, and teaching I should only take the teaching, healthcare and government jobs. I've racked my brain and I can't think of an efficient solution to this. I'm sure I should use some sort of looping method but I've never done a loop like this. Any help is appreciate. Thank you! Data I have: WORKRETAIL WORKRETAILNAME WORKRETAILADD WORKGOV WORKGOVNAME WORKGOVADD WORKTEACH WORKTEACHNAME WORKTEACHADD WORKHEALTH WORKHEALTHNAME WORKHEALTHADD WORKOTHER WORKOTHERNAME WORKOTHERADD Yes Salon 123 ABC St, California Yes Agency 123 ABC St, California Yes Elementary 123 ABC St, California Yes Hospital 123 ABC St, California No Data I need: WORKNAME1 WORKADD2 WORKNAME2 WORKADD2 WORKNAME3 WORKADD3 Agency 123 ABC St, California Elementary 123 ABC St, California Hospital 123 ABC St, California

cgates · ‎10-12-2020

Hi Kurt. I'm positive I'm looking at the same network share which is why I can't identify the root cause. This program has been running automated every day for the past 30 days and it has only aborted twice due to this issue. We've changed nothing in the code. After the 1st abort, we even set the automation to run a couple of hours later thinking maybe there wasn't enough time for SAS to pick up the new modified date. But here we are again.

cgates · ‎10-12-2020

Hello, I'm using SAS 9.4 for this code on a Windows computer. A .bat file runs this program each morning and my first lines of code use finfo to find the last modified date of two csv files I'm importing. The csv files gets overwritten each morning prior to the SAS program running, so if the last modified date is not equal to today's date, I force the program to abort. The issue is that the program has aborted twice now (not consecutively - once a week ago and this morning) because finfo generated an incorrect last modified date. This morning it says the csv files were last updated on Oct. 11th, but they were actually updated this morning on Oct. 12th. What could be causing this? Code below from this morning's run. NOTE: PROCEDURE PRINTTO used (Total process time): real time 0.03 seconds cpu time 0.00 seconds The SAS System 06:00 Monday, October 12, 2020 Engine: V9 filename fileref "J:\Tests.csv"; filename filerefa "J:\Facilities.csv"; data a(drop=fidlab fidreg); infile fileref truncover obs=1; infile filerefa truncover obs=1; fidlab=fopen("fileref"); fidreg=fopen("filerefa"); moddatelab=input(finfo(fidlab,"Last Modified"),anydtdte32.); moddatereg=input(finfo(fidreg,"Last Modified"),anydtdte32.); format moddatelab moddatereg date9.; CALL SYMPUT("labtime",put(moddatelab,date9.)) ; CALL SYMPUT("regtime",put(moddatereg,date9.)) ; run; NOTE: The infile FILEREF is: Filename=J:\Tests.csv, RECFM=V,LRECL=32767,File Size (bytes)=2407224, Last Modified=11Oct2020:03:15:16, Create Time=15Sep2020:11:40:50 NOTE: The infile FILEREFA is: Filename=J:\Facilities.csv, RECFM=V,LRECL=32767,File Size (bytes)=41733, Last Modified=11Oct2020:03:17:01, Create Time=15Sep2020:11:41:54 However, when I check the filepath to see when the two csv files were last updated, this is what I see. EDIT: Worth noting that the two csv files get updated each morning at 12:17 AM and 12:15 AM MST. The program is run on an EST computer so the 11Oct2020:03:17 and 11Oct2020:03:15 times are from yesterday's update to to the CSV files. I'm not sure why SAS picked up that one instead of this morning's modification.

cgates · ‎09-09-2020

Update for this post. I worked with a SAS Specialist and we identified that the issue was only occurring when trying to save in the default enhanced editor. When we opened the program editor we could save with no issues. He said he’d go digging for a solution, but a few days later the issue resolved itself. Our IT team is baffled as they said they hadn’t changed anything and the SAS Specialist never found a solution but things are working again.

Online Status	Offline
Date Last Visited	2 weeks ago

Re: How To Download a Google Sheet .csv through SAS

Re: How to Stop & Restart Query Every 30 min

Re: How to Stop & Restart Query Every 30 min

How to Stop & Restart Query Every 30 min

Re: Need to Calculate Days Between All Possible Combinations of 6 Date...

Re: Need to Calculate Days Between All Possible Combinations of 6 Date...

Need to Calculate Days Between All Possible Combinations of 6 Date Var...

How Can I Do a Fuzzy Character Merge using Hash Objects in SAS?

Re: How To Download a Google Sheet .csv through SAS

How To Download a Protected Google Sheet .csv through SAS

Re: How to Stop & Restart Query Every 30 min

Re: How to Stop & Restart Query Every 30 min

Re: How to Stop & Restart Query Every 30 min

Re: How to Stop & Restart Query Every 30 min

Re: Physical file does not exist - Can't Output File to SharePoint Map...

Cannot Save Modifications to Existing SAS Files - Only For A Certain S...

Re: Cannot Save Modifications to Existing SAS Files - Only For A Certa...

Re: How To Download a Google Sheet .csv through SAS

Re: How to Stop & Restart Query Every 30 min

Re: How to Stop & Restart Query Every 30 min

How to Stop & Restart Query Every 30 min

Re: Need to Calculate Days Between All Possible Combinations of 6 Date...

Re: Need to Calculate Days Between All Possible Combinations of 6 Date...

Need to Calculate Days Between All Possible Combinations of 6 Date Var...

How Can I Do a Fuzzy Character Merge using Hash Objects in SAS?

Re: How To Download a Google Sheet .csv through SAS

How To Download a Protected Google Sheet .csv through SAS

Re: Need to Loop Through 36 Character Variables & Create Six New Hiera...

Need to Loop Through 36 Character Variables & Create Six New Hierarchi...

Re: FINFO Function Generating Incorrect Last Modified Date

FINFO Function Generating Incorrect Last Modified Date

Re: Cannot Save Modifications to Existing SAS Files - Only For A Certa...