About axescot78

axescot78

data flag_vars; set fake_data_nodupkey; by userID ; prev_response = lag(response); prev_code = lag(code); if first.userID then do; prev_response = response; prev_code = code; end; response_change = 0; if response ne prev_response then response_change = 1; code_change = 0; if code ne prev_code then code_change = 1; run;

axescot78

I have long data and wrote code to track changes in 2 variables over time. However, the second entry for each userID is wrong. My data, code, and screenshot of results below. What is wrong with the code? data fake_data; input userID $ event_date date9. response $ code $; format event_date date9.; datalines; 1693 01Dec2014 Y V 1693 01Jan2015 Y V 1693 01Feb2015 Y V 1693 01Mar2015 M G 1693 01Apr2015 M G 1693 01May2015 M G 1129 01Feb2018 Y V 1129 01Mar2018 Y V 1129 01Apr2018 N R 1129 01May2018 N R 1129 01Jun2018 M R 1129 01Jul2018 M R 1345 01Aug2016 N R 1345 01Sep2016 N R 1345 01Oct2016 N R 1345 01Nov2016 N R 1345 01Dec2016 M R 1345 01Jan2017 M G 1345 01Feb2017 Y G 1345 01Mar2017 Y G 1345 01Apr2017 Y G 1345 01May2017 Y G ; proc sort data = fake_data out=fake_data_nodupkey nodupkey; by userID event_date; run; data flag_vars; set fake_data_nodupkey; by userID event_date; if first.userID then do; prev_response = response; prev_code = code; end; else do; prev_response = lag(response); prev_code = lag(code); end; response_change = 0; if response ne prev_response then response_change = 1; code_change = 0; if code ne prev_code then code_change = 1; run; proc print data=flag_vars; run;

axescot78 · ‎01-28-2024

@Kurt_Bremser I don't completely understand the difference between the 2 methods that you're asking about. I ended up counting the number of months that's not missing and subtracting by the total number of months to get the missing months. I left it as months but easy enough to convert to days, especially when it's over a span of years.

axescot78 · ‎01-27-2024

Sorry about that. I couldn't get the code to work right so I tried another approach. Here is the data which can be pasted in a txt file and saved as a csv, and then there is code to import the file. This won't match the screenshot in my original post. Please ignore that screenshot. The data below is what was intended. Thank you. User_ID,Vst_dt1,Vst_dt2,Vst_dt3,Vst_dt4,Vst_dt5,Vst_dt6,Vst_dt7,Vst_dt8,Vst_dt9,Vst_dt10 1,1-Feb-23,1-Mar-23,1-Apr-23,1-May-23,1-Jun-23,1-Jul-23,1-Aug-23,1-Sep-23,1-Oct-23,1-Nov-23 2,.,1-Mar-23,1-Apr-23,1-May-23,.,.,1-Aug-23,1-Sep-23,1-Oct-23,. 3,.,.,1-Apr-23,1-May-23,.,.,.,.,.,. 4,.,.,.,1-May-23,1-Jun-23,1-Jul-23,.,.,1-Oct-23,1-Nov-23 5,1-Feb-23,1-Mar-23,1-Apr-23,.,.,.,1-Aug-23,1-Sep-23,.,. proc import datafile="your_path/Book1.csv" dbms=csv out=fake_data replace; run;

axescot78 · ‎01-25-2024

I have monthly data and am trying to calculate the amount of time spent in visit with gaps in between visits. So in the example below, rows 1 and 5 should calculate 59 days and row 2 should calculate 62 (the number of days in each month where there's a visit). How can I calculate it and account for gaps? Here is the code that produces that table. It has more data (couldn't get it to work right) but still the same idea. data fake_data; input User_ID $ Vst_dt1 date9. Vst_dt2 date9. Vst_dt3 date9. Vst_dt4 date9. Vst_dt5 date9. Vst_dt6 date9. Vst_dt7 date9. Vst_dt8 date9. Vst_dt9 date9. Vst_dt10 date9.; format User_ID $2. Vst_dt1 date9. Vst_dt2 date9. Vst_dt3 date9. Vst_dt4 date9. Vst_dt5 date9. Vst_dt6 date9. Vst_dt7 date9. Vst_dt8 date9. Vst_dt9 date9. Vst_dt10 date9.; datalines; 01 01Feb2023 01Mar2023 01Apr2023 01May2023 01Jun2023 01Jul2023 01Aug2023 01Sep2023 01Oct2023 01Nov2023 02 . 01Mar2023 01Apr2023 01May2023 . . 01Aug2023 01Sep2023 01Oct2023 . 03 . . 01Apr2023 01May2023 . . . . . . 04 . . . 01May2023 01Jun2023 01Jul2023 . . 01Oct2023 01Nov2023 05 01Feb2023 01Mar2023 01Apr2023 . . . 01Aug2023 01Sep2023 . . ;

axescot78 · ‎01-16-2024

I am trying to merge data from one data set to another based on first and last name. I run the following code: proc sql; create table merged_data as select * from pull_data where strip(upcase(last_name)) in (select strip(upcase(NameLast)) from reference_data) and strip(upcase(first_name)) in (select strip(upcase(NameFirst)) from reference_data); quit; This matches all last names without regard to first name. So in the example data below, fake_l_name_1 will match with fake_f_name_1 and fake_f_name_2, rather than with fake_f_name_1 only. Can anyone tell me why proc sql is ignoring the 'and' statement? data pull_data; input userID $4. first_name $14. last_name $14. ; datalines; 1693 fake_f_name_1 fake_l_name_1 1693 fake_f_name_1 fake_l_name_1 1693 fake_f_name_1 fake_l_name_1 1693 fake_f_name_1 fake_l_name_1 1693 fake_f_name_1 fake_l_name_1 1693 fake_f_name_1 fake_l_name_1 1129 fake_f_name_2 fake_l_name_1 1129 fake_f_name_2 fake_l_name_1 1129 fake_f_name_2 fake_l_name_1 1129 fake_f_name_2 fake_l_name_1 1129 fake_f_name_2 fake_l_name_1 1129 fake_f_name_2 fake_l_name_1 1345 fake_f_name_3 fake_l_name_2 1345 fake_f_name_3 fake_l_name_2 1345 fake_f_name_3 fake_l_name_2 1345 fake_f_name_3 fake_l_name_2 1345 fake_f_name_3 fake_l_name_2 1345 fake_f_name_3 fake_l_name_2 1345 fake_f_name_3 fake_l_name_2 1345 fake_f_name_3 fake_l_name_2 1345 fake_f_name_3 fake_l_name_2 1345 fake_f_name_3 fake_l_name_2 1492 fake_f_name_4 fake_l_name_2 1492 fake_f_name_4 fake_l_name_2 1492 fake_f_name_4 fake_l_name_2 1492 fake_f_name_4 fake_l_name_2 ; data reference_data; input NameFirst $14. NameLast $14. ; datalines; fake_f_name_1 fake_l_name_1 fake_f_name_2 fake_l_name_2 fake_f_name_3 fake_l_name_3 fake_f_name_4 fake_l_name_4 fake_f_name_5 fake_l_name_5 fake_f_name_6 fake_l_name_6 fake_f_name_7 fake_l_name_7 fake_f_name_8 fake_l_name_8 ;

axescot78 · ‎01-05-2024

Is it possible to import an Excel file using SAS data step?

axescot78 · ‎12-13-2023

I have data by user ID that is receive on a monthly basis. There is one variable that can change, once or twice or more. I want to be create a flag for those individuals that have this variable changed and record that changes. What I want: UserID changed res1 res2 res3 1693 1 Y M 1129 1 Y N M 1345 1 N M Y What I have: data fake_data; input userID $ date date9. response $ ; format date date9.; datalines; 1693 01Dec2014 Y 1693 01Jan2015 Y 1693 01Feb2015 Y 1693 01Mar2015 M 1693 01Apr2015 M 1693 01May2015 M 1129 01Feb2018 Y 1129 01Mar2018 Y 1129 01Apr2018 N 1129 01May2018 N 1129 01Jun2018 M 1129 01Jul2018 M 1345 01Aug2016 N 1345 01Sep2016 N 1345 01Oct2016 N 1345 01Nov2016 N 1345 01Dec2016 M 1345 01Jan2017 M 1345 01Feb2017 Y 1345 01Mar2017 Y 1345 01Apr2017 Y 1345 01May2017 Y ;

axescot78 · ‎11-30-2023

I get an error like this: NOTE: Invalid argument to function INPUT at line 110 column 167. However, since it is in a macro, I don't have line numbers in the log to find the exact location. How do I find which line the error is occurring?

axescot78 · ‎06-29-2023

I have a dataset of 5000 observations and about 200 variables by user id. A little more than 1000 users have multiple entries. These aren't duplicate entries. They have different values for some of those 200 variables. They are not the same variables for all users that have multiple entries. See example data set below. data fake_data; input user_id 3. Var1 2. Var2 3. Var3 2. Var4 3. Var5 2. Var6 $; datalines; 03 0 10 0 31 0 . 03 0 11 1 31 1 . 04 0 00 1 10 0 . 04 0 47 0 21 1 . 04 0 47 0 21 0 . 04 0 74 0 21 0 . 05 0 00 0 22 1 . 05 0 15 0 22 0 . 06 0 00 9 26 0 . 06 0 00 9 26 0 . 06 0 00 9 26 0 . 07 0 00 0 72 0 . 07 5 00 0 84 1 . 07 0 00 9 84 0 . 08 0 00 0 32 1 . 08 0 00 9 32 0 . 09 0 54 0 54 0 Excel 09 0 54 0 54 0 Excel 10 0 73 0 18 0 . 10 0 00 9 18 0 . 10 0 23 0 18 1 . 10 0 93 0 18 0 . 11 0 37 0 12 1 . 11 0 43 0 12 0 . 11 0 43 0 12 1 . 12 0 40 0 15 0 . 12 0 00 0 15 0 . 13 1 00 0 16 0 . 13 0 63 0 16 1 . ; I need to turn this into a wide data set where it's one row per user id without having columns that duplicate data. That is, when there is different values make column wide, do not duplicate columns with same value. How can I do this when the data is so random? I can work in SAS, Excel, or R.

axescot78 · ‎01-27-2023

I have 2 data sets that I am trying to link by an alphanumeric variable (job code). I already found that one data set contains "non-breaking spaces" (which I didn't know exist). I am finding some of the codes still are not matching. For example, I have a code "AB000". When I do a proc freq before linking these data sets, I find two "AB000" listed. The search function in SAS results window finds both entries using "AB000". I also tried matching with "%AB000%" and using compress to account for any characters that I may not be seeing, and still getting the same result. I tried exporting the proc freq to excel and original data set to csv, and still I am not seeing a difference. Has anyone had experience with this?

axescot78 · ‎03-29-2022

I found this to work: data want; set fake_data; /* assigns first non-missing follow up values */ do; lks_v2 = visit_1; lks_v3 = coalesce(of visit_2 - visit_1); lks_v4 = coalesce(of visit_3 - visit_1); lks_v5 = coalesce(of visit_4 - visit_1); end; run;

axescot78 · ‎03-29-2022

this didn't work 😞 how do I specify going to the prior element? v[i-1] did not work.

axescot78 · ‎03-29-2022

I have patient data where they come in on the first visit and are scheduled follow up visits. The values are binary except when they miss a visit (blank). I would like to create 4 new variables for each visit that will be the last known non-missing status from the last visit. So from the fake data below, for the last patient (0219): lks_v2 = 0 lks_v3 = 0 lks_v4 = 0 lks_v5 = 1 I know I can do this with a bunch of if statements but is there a more elegant way? I was thinking to use coalesce statements. data fake_data; input patID $ 1-4 visit_1 $ 6 visit_2 $ 8 visit_3 $ 10 visit_4 $ 12 visit_5 $ 14 ; datalines; 1693 1 1 . . 0 0659 0 . . 1 0 0389 1 0 . 1 0 0744 1 . 0 1 0 1129 0 1 . . 1 1003 0 0 1 . 1 0734 1 . 0 . 1 1345 1 . 1 . 0 0219 0 . . 1 1 ;

axescot78 · ‎02-09-2022

I have dataset with a long list of variables. I am interested in selecting those variables that start with: mon_ wed_ fri_ and end with: _07 _11 I am also interested in those that start with: sat_ and end with: _11 There could be any character and any number of character in between. How can I select these programmatically?

Online Status	Offline
Date Last Visited	3 weeks ago

Re: track changes over time

track changes over time

Re: calculate time spent accounting for gaps

Re: calculate time spent accounting for gaps

calculate time spent accounting for gaps

proc sql ignoring and statement

Import Excel file using SAS data step

tracking changes in a variable - long data

finding error in SAS macro

turning random non-duplicate entries into columns

Re: randomly missing data

getting the last known status from multiple time points

randomly missing data

Re: search for first encounter to match cell value and return another ...

Re: track changes over time

track changes over time

Re: calculate time spent accounting for gaps

Re: calculate time spent accounting for gaps

calculate time spent accounting for gaps

proc sql ignoring and statement

Import Excel file using SAS data step

tracking changes in a variable - long data

finding error in SAS macro

turning random non-duplicate entries into columns

SAS sees the same value as 2 different values

Re: getting the last known status from multiple time points

Re: getting the last known status from multiple time points

getting the last known status from multiple time points

selecting variables based on name prefix and suffix