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: 
Patrick
2 weeks ago
  • 9,731 Posts
  • 885 Likes Given
  • 1,047 Solutions
  • 4,447 Likes Received

Re: How to use macro variable that was created in another macro

by Opal | Level 21 in SAS Programming
‎09-25-2024 12:44 AM
1 Like
‎09-25-2024 12:44 AM
1 Like
Per default a macro variable created within a macro is local in scope. To use a macro variable outside of the macro where it gets created and populated it must be global.  Add below line to your macro definition: %global conn_creds; ... View more

Re: How to program across rows to decide eligibility

by Opal | Level 21 in SAS Programming
‎09-24-2024 04:06 AM
‎09-24-2024 04:06 AM
The following code returns for your sample data what you've asked for. data want; infile datalines truncover dsd; input Person_ID $ Begin_Date :mmddyy10. End_Date :mmddyy10.; format Begin_Date End_Date mmddyy10.; datalines; 111111,7/14/2018,7/19/2018 111111,7/26/2018,7/30/2018 111111,11/17/2018,11/26/2018 222222,5/17/2018,5/19/2018 444444,12/21/2018,12/28/2018 555555,4/28/2018,5/3/2018 555555,5/6/2018,5/10/2018 555555,11/26/2018,11/29/2018 666666,1/14/2018,1/24/2018 666666,1/29/2018,1/31/2018 666666,2/4/2018,2/5/2018 666666,2/7/2018,2/12/2018 666666,8/18/2018,8/23/2018 666666,10/3/2018,10/16/2018 666666,11/4/2018,11/5/2018 666666,11/5/2018,11/12/2018 ; run; data have; infile datalines truncover dsd; input Person_ID $ Begin_Date :mmddyy10. End_Date :mmddyy10. Eligible; format Begin_Date End_Date date9.; if _n_=1 then do; dcl hash h1(dataset:'want'); h1.defineKey('Person_ID', 'Begin_Date', 'End_Date'); h1.defineDone(); end; want_flg= h1.check()=0; datalines; 111111,3/13/2018,3/15/2018,0 111111,4/24/2018,4/26/2018,0 111111,7/13/2018,7/14/2018,0 111111,7/14/2018,7/19/2018,1 111111,7/26/2018,7/30/2018,1 111111,11/17/2018,11/26/2018,1 222222,5/1/2018,5/4/2018,0 222222,5/17/2018,5/19/2018,1 333333,3/8/2018,3/9/2018,0 333333,4/17/2018,4/26/2018,0 333333,6/16/2018,6/20/2018,0 333333,7/3/2018,7/4/2018,0 333333,7/23/2018,7/24/2018,0 333333,8/9/2018,8/10/2018,0 333333,9/8/2018,9/11/2018,1 333333,9/11/2018,9/14/2018,1 333333,9/23/2018,9/26/2018,0 444444,7/5/2018,7/7/2018,0 444444,12/21/2018,12/28/2018,1 555555,4/28/2018,5/3/2018,1 555555,5/6/2018,5/10/2018,1 555555,11/26/2018,11/29/2018,1 666666,1/14/2018,1/24/2018,1 666666,1/29/2018,1/31/2018,1 666666,2/4/2018,2/5/2018,1 666666,2/7/2018,2/12/2018,1 666666,8/18/2018,8/23/2018,1 666666,8/24/2018,8/25/2018,1 666666,8/28/2018,9/3/2018,1 666666,9/6/2018,9/11/2018,1 666666,9/13/2018,9/16/2018,1 666666,9/22/2018,9/23/2018,0 666666,10/3/2018,10/16/2018,1 666666,11/4/2018,11/5/2018,1 666666,11/5/2018,11/12/2018,1 666666,12/25/2018,12/28/2018,1 666666,12/30/2018,12/31/2018,0 ; run; proc sort data=have; by Person_ID descending Begin_Date; run; data derived; set have; by Person_ID descending Begin_Date; length derived_flg check_date 8; format check_date date9.; retain check_date; if first.person_id and Eligible=1 then check_date='31Dec5999'd; else if Eligible=0 then check_date=Begin_Date; if Eligible=0 then delete; diff=check_date-Begin_Date; derived_flg= check_date-Begin_Date>30; run; proc print data=derived; run;       ... View more

Re: How to program across rows to decide eligibility

by Opal | Level 21 in SAS Programming
‎09-24-2024 12:18 AM
‎09-24-2024 12:18 AM
But if a record with any ineligible record within 30 days for the same Person_ID, the record should be dropped. WITHIN means Before And After? Why is below record WANT.... 111111,7/14/2018,7/19/2018 ...given your HAVE data also contains below record with eligible=0? 111111,7/13/2018,7/14/2018,0 ... View more

Re: Add a blank row below a row in a dataset

by Opal | Level 21 in SAS Programming
‎09-23-2024 11:57 PM
‎09-23-2024 11:57 PM
You shouldn't mix display layout with data structure. Proc Report would also allow to add such empty lines at the beginning or the end of a group. But o.k., here you go: data want; set dummy; output; if col1 =: 'PLACEBO-CONTROLLED' then do; call missing(col1); col0='week 2'; output; end; run;   ... View more

Re: Add a blank row below a row in a dataset

by Opal | Level 21 in SAS Programming
‎09-23-2024 11:26 PM
‎09-23-2024 11:26 PM
I assume you want to add such empty rows for some reporting purposes. If so then that would be a very suboptimal approach. I suggest you explain us what you have and what you want to achieve for us to provide some advice. Below code to answer your question (but... don't do it. SAS tables and Excel sheets are very different things). data dummy; length col1 $200; col1="BASELINE Heart Rate";output; call missing(col1); output; /* and so on */ col1='PLACEBO-CONTROLLED Heart Rate';output; col1='WEEK 2 Heart Rate';output; col1='WEEK 4 Heart Rate';output; col1='WEEK 8 Heart Rate';output; col1='WEEK 12 Heart Rate';output; col1='WEEK 18 Heart Rate';output; col1='BASELINE SYSBP';output; col1='PLACEBO-CONTROLLED SYSBP';output; col1='WEEK 2 SYSBP';output; col1='WEEK 4 SYSBP';output; col1='WEEK 8 SYSBP';output; col1='WEEK 12 SYSBP';output; col1='WEEK 18 SYSBP';output; col1='BASELINE DIABP';output; col1='PLACEBO-CONTROLLED DIABP';output; col1='WEEK 2 DIABP';output; col1='WEEK 4 DIABP';output; col1='WEEK 8 DIABP';output; col1='WEEK 12 DIABP';output; col1='WEEK 18 DIABP';output; RUN;   ... View more

Re: How to use array to replace missing value in multiple variables to...

by Opal | Level 21 in SAS Programming
‎09-23-2024 10:41 PM
1 Like
‎09-23-2024 10:41 PM
1 Like
You could use a format for recoding and if only required for display (print) purposes then even apply it directly to the numerical variables. data df1; a = .; b=1; c=1; output; a = 1; b=.z; c=1; output; a = 1; b=1; c=.; output; run; proc format; value num2char low-high =[f16.] other ='NA' ; run; /* if only for display purposes */ proc print data=df1; format a b c num2char.; run; /* if storing recoded value in character vars */ data want; set df1; array _invars{3} a b c; array _inval_{3} $16; do i=1 to dim(_invars); _inval_[i]=put(_invars[i],num2char.); end; run; proc print data=want; run;   ... View more

Re: Password extract within SAS code

by Opal | Level 21 in Administration and Deployment
‎09-23-2024 06:41 PM
2 Likes
‎09-23-2024 06:41 PM
2 Likes
The clean way is as @freshstarter describes.    A workaround could be to create the libname with your credentials in your EG autoexec and then only use the libref in your code.  Don't store the password in clear text but encoded using Proc Pwencode   In a Unix/Linux environment: You could also create an environment variable via your .profile in your home directory that contains the encoded password and then use this environment variable in your libname statement in code. ...pw="%sysget(<environment variable created in .profile>)"... ... View more

Re: SAS Access to Postgres | Error

by Opal | Level 21 in Administration and Deployment
‎09-23-2024 06:16 PM
1 Like
‎09-23-2024 06:16 PM
1 Like
I believe you can pass these two database parameters via SAS libname/connection option CONOPTS='connection_options'   When copy/pasting the error message into chatGPT or Copilot the instructions are to update on the Postgres side. Below the first step as received as answer:   Here are some steps to resolve this issue: Edit the  pg_hba.conf  File: Log into your PostgreSQL server. Locate the  pg_hba.conf  file, typically found in the data directory (e.g.,  /var/lib/pgsql/data/ ). Add an entry for your host. For example: host all all x.x.x.x/32 md5 Replace  x.x.x.x  with the actual IP address you’re connecting from. ...... ... View more

Re: How to program across rows to decide eligibility

by Opal | Level 21 in SAS Programming
‎09-23-2024 01:08 AM
‎09-23-2024 01:08 AM
@lichee I'm still struggling to understand your selection logic. Like: If WITHIN 30 days means Before and After. Given that record selection and collapsing records are two different problems and that record selection needs resolution first, I suggest that you provide us with a HAVE data set that marks all the records that you want selected. I also suggest that you use date format in the data and your explanations that's international. MDY formats are really hard to read for most non-US people.   Below the sample data structure I'd suggest. You will need to amend the data for the last column  select_desired_flg data have; infile datalines truncover dsd; input Person_ID $ Begin_Date :date9. End_Date :date9. Eligible select_desired_flg; format Begin_Date End_Date date9.; datalines; 111111,13MAR2018,15MAR2018,0,0 111111,24APR2018,26APR2018,0,0 111111,13JUL2018,14JUL2018,0,0 111111,14JUL2018,19JUL2018,1,1 111111,26JUL2018,30JUL2018,1,1 111111,17NOV2018,26NOV2018,1,1 222222,01MAY2018,04MAY2018,0,0 222222,17MAY2018,19MAY2018,1,1 333333,08MAR2018,09MAR2018,0,0 333333,17APR2018,26APR2018,0,0 333333,16JUN2018,20JUN2018,0,0 333333,03JUL2018,04JUL2018,0,0 333333,23JUL2018,24JUL2018,0,0 333333,09AUG2018,10AUG2018,0,0 333333,08SEP2018,11SEP2018,1,0 333333,11SEP2018,14SEP2018,1,0 333333,23SEP2018,26SEP2018,0,0 444444,05JUL2018,07JUL2018,0,0 444444,21DEC2018,28DEC2018,1,1 555555,28APR2018,03MAY2018,1,1 555555,06MAY2018,10MAY2018,1,0 555555,26NOV2018,29NOV2018,1,1 666666,14JAN2018,24JAN2018,1,0 666666,29JAN2018,31JAN2018,1,0 666666,04FEB2018,05FEB2018,1,0 666666,07FEB2018,12FEB2018,1,0 666666,18AUG2018,23AUG2018,1,0 666666,24AUG2018,25AUG2018,1,0 666666,28AUG2018,03SEP2018,1,0 666666,06SEP2018,11SEP2018,1,0 666666,13SEP2018,16SEP2018,1,0 666666,22SEP2018,23SEP2018,0,0 666666,03OCT2018,16OCT2018,1,0 666666,04NOV2018,05NOV2018,1,0 666666,05NOV2018,12NOV2018,1,0 666666,25DEC2018,28DEC2018,1,0 666666,30DEC2018,31DEC2018,0,0 ; run; I also suggest that you provide us with a re-formulated set of your selection logic. Given the answers you've got so far I assume that I'm not the only one struggling with your narrative.    ... View more

Re: proc sql select ignore if variable does not exist

by Opal | Level 21 in SAS Procedures
‎09-22-2024 05:51 AM
‎09-22-2024 05:51 AM
@trevand wrote: @Patrick I did exactly what you suggested. I appended all annual files and then saved them as a single year file since at the end I need single year files. How would you  implement a check prior to the SQL and then only use the column in the SQL if it exists?   You could take an approach that creates stable table structures which will make downstream processing easier. If you concatenate multiple SAS table via a SAS data step then the concatenated table will contain any variable that exists in at least one source table. The variable will just be missing for the years where it didn't exist in source. You could for example concatenate ALL the yearly tables you've got to create the table layout (without any rows in it) and then use this layout file as part of your concatenation. Below some sample code to illustrate the approach. /* create sample data */ data year_2020(drop=var1 var2) year_2021(drop=var2 var3) year_20203(drop=var4); array vars {*} var1-var4 (4*100); do i=1 to 10; output; end; run; /* create mapping file */ %let save_obs=%sysfunc(getoption(obs,keyword)); options obs=0; data mapping; stop; set year_:; run; options &save_obs; /* create concatenated table for selecte years with stable structure */ data selected_years; set mapping year_2020 year_2021; run; /* list table structure */ proc contents data=selected_years; run; ... View more

Re: How to program across rows to decide eligibility

by Opal | Level 21 in SAS Programming
‎09-20-2024 12:37 AM
‎09-20-2024 12:37 AM
You're raising two distinct problems here: 1: how to select records, 2: how to collapse the selected records.   For record selection: That your date ranges can overlap or that one date range can even be contained within another really complicates things. In such a scenario simply ordering the data by begin date and then test against the last not eligible record might not be sufficient for your real data. What for example should happen in below scenario? person_id begin_date end_date eligible 1 01jan2024 31jan2023 1 1 10jan2024 15jan2024 0   Above is eventually not possible in your real data but if it is then you need to amend your sample data so it covers all possible real cases - and then test your selection logic against it before we even get into coding.   For collapsing the selected records: Also here overlapping date ranges can complicate things. Try to provide us with sample data that cover all possible cases for selected records to be collapsed. Especially provide sample data with more multiple records that need to get collapsed into a single record (including a record where a data range is fully contained within another date range so that the records don't sort "properly" by begin date).   Below code returns your WANT table but because of assumed missing cases I don't believe it would work for your real data. proc sort data=have; by person_id descending Begin_Date; run; /* select records */ data selected_records; set have; by person_id descending begin_date; retain check_date; if first.person_id then check_date='01jan1960'd; if eligible=0 then do; check_date=End_Date; delete; end; if begin_date-check_date<=30 then delete; run; ... View more

Re: proc sql select ignore if variable does not exist

by Opal | Level 21 in SAS Procedures
‎09-19-2024 08:15 PM
‎09-19-2024 08:15 PM
If SQL throws an error then you can’t ignore it. You could implement a check prior to the SQL and then only use the column in the SQL if it exists.  You could also concatenate all the source tables (could even be a data step view) and then process all the data in a single SQL. If you concatenate the source tables with a data step then the column will exist and be missing for the source tables where it didn’t exists. The only challenge with creating such a intermediary single source table: If in the source tables same named variables got different attributes (lengths or type) the you will still need to implement some further pre-processing to deal with it in order to avoid string truncation or even errors due to type mismatch.  ... View more

Re: Indexing a variable for values specified within an array

by Opal | Level 21 in SAS Programming
‎09-19-2024 07:30 AM
‎09-19-2024 07:30 AM
If your source variable SAMPLE_TIME_POINT contains data like in below sample Have table and you can match against the full string then code like below could work. data have; input SAMPLE_TIME_POINT $; datalines; PRE 1HR 2HR 4HR 24HR 96HR ; proc format; value $map_tpts 'PRE' = 'PRE-DOSE' '1HR' = '1 HOUR POST-DOSE' '2HR' = '2 HOURS POST-DOSE' '4HR' = '4 HOURS POST-DOSE' '24HR'= '24 HOURS POST-DOSE' '96HR'= '96 HOURS POST-DOSE' ; value $tptnums 'PRE' = '0' '1HR' = '1' '2HR' = '2' '4HR' = '3' '24HR'= '24' '96HR'= '96' ; run; data want; set have; PCTPT=put(SAMPLE_TIME_POINT,$map_tpts.); PCTPTNUM=put(SAMPLE_TIME_POINT,$tptnums.); run; proc print data=want; run; ... View more

Re: Using SAS macro with substr

by Opal | Level 21 in SAS Programming
‎09-19-2024 06:38 AM
‎09-19-2024 06:38 AM
@CathyVI From the looks of it the solution from this discussion could also be applicable to your case. ... View more

Re: Automating Data Load from Hive to CAS

by Opal | Level 21 in SAS Viya
‎09-19-2024 06:29 AM
1 Like
‎09-19-2024 06:29 AM
1 Like
Given that CAS tables are in-memory that gets wiped out whenever the server needs to restart (like for maintenance) I'd go for a daily full-load. Depending on how your environment looks like a load from Hadoop executes potentially fully in parallel and can be very fast.   I'd certainly go for a SAS coding option that's scheduled. If you must load new data during business hours then I'd also first load into another CAS table and then "swap" the tables in CAS to keep the outage for report consumers to a minimum.   One of the reasons that I'd go for code: It allows you to explicitly defined how to map source to target columns because for example Hive String data types will per default result in a 1024 Varchar CAS column. As a rule of thumb: Don't use CAS Varchar for columns where the max string length is below 16 bytes (use CHAR instead) and also use CHAR where the string length is constant.   Also: If the data is used for reports that mostly show detail data aggregated by some categorical variable (example: transactional data summed by account) then it's also beneficial for report performance to load all the data that belongs to the same value of this categorical variable (example: the same account) onto the same cas node. This is something you can do with code - like the loadTable action ... View more
Contact Me
Online Status
Offline
Date Last Visited
2 weeks ago
Group Hubs for Patrick
View All
Likes given to
Likes from
Quick links