About Walternate

Walternate · ‎08-06-2015

Hi, I have a dataset with multiple rows per person, which I am reducing to one row per person based on values of other variables. There's an ID variable, two categorical variables, and a date variable. Categ_1 can differ across the same ID, but categ_2 will always have the same value for the same ID (or more accurately, it can be missing, but there won't be one person who has two separate non-missing values for this variable). ID categ_1 categ_2 date 1 a 1/2/2015 1 b x 2/14/2015 2 c y 10/9/2013 2 c y 5/4/2012 Basically, my logic for which record to keep for each person is this: 1. If any of their values of categ_1=a, that record should be kept 2. If none of their values of categ_1=a, then the record with the most recent date should be kept So all I did was sort by categ_1 and date (descending), then output the top record. The problem is that none of the records where categ_1=a has a value for categ_2. Since categ_2 will always be the same within each ID number, I want to add a step where I fill categ_2 using other row(s) of that ID number before outputting. The way I have it now, the top record for ID1 will output, which is right, but I want it to have categ_2=x before it outputs to the one person/one row dataset. Any help is much appreciated.

Walternate · ‎08-04-2015

Hi, I have two datasets (one from Excel, one from SAS) that each have first and last name variables. I am trying to join the two datasets on first name and last name: proc sql; create table want as select * from have1 a inner join have2 b on (a.have1_first_name=b.have2_first_name) and (a.have1_last_name=b.have1_last_name); quit; The join worked on about 2/3 of the dataset, but not the other 1/3. The problem is that I can't figure out why the join isn't working on the remaining 1/3. I've looked some non-joiners up by hand and there are no initials or capitalization issues or anything like that--the values look identical to me, but still aren't recognized as identical by SAS. I need some ideas on how to troubleshoot and figure out the disparity between the values so I can complete the join. Thanks!

Walternate · ‎08-03-2015

Hi, I have two datasets with identical variables (Dataset2 is an updated version of Dataset1). Each dataset is unique at the person level and contains ID and about 20 categorical variables. Only a subset of those in Dataset1 are in Dataset2--that is, if any person in Dataset1's information had changed, it would be reflected in Dataset2, but if their information stayed the same, they would not have a record in Dataset2: Dataset1: ID categ_var1 categ_var2...etc 1 a d 2 a g 3 b d 4 c e 5 b f Dataset2: ID categ_var1 categ_var2...etc 2 a b 5 c f What I want is to combine the two datasets such that I have one record for each person, but if they have a record in Dataset2, SAS keeps that record instead of the one from Dataset1: ID categ_var1 categ_var2...etc 1 a d 2 a b 3 b d 4 c e 5 c f Any help is much appreciated.

Walternate · ‎07-29-2015

Hi, I have a dataset with a categorical variable filled with two-letter values. Some of the values are missing. ID Categ_var 1 AD 1 1 BA 2 BR 2 CB 2 2 BR 3 AZ I am trying to filter values of Categ_var that have a B in them, like this: proc sql; create table want as select * from have where categ_var not like '%B%'; quit; The problem is that doing this causes SAS to remove the rows in which Categ_var is missing as well, but I want to keep those rows. I tried amending it like this: proc sql; create table want as select * from have where (categ_var=" " or categ_var not like '%B%'); quit; But the same thing happened. Any help is much appreciated.

Walternate · ‎07-27-2015

Hi, I have data at the month/year level with variables ID, target date, mth/year, mth/year distance from target date, total events for that mth/year, total type A events for that mth/year, and total type B events for that mth/year. All events are either type A or type B, that is, the last two columns will sum to the total events column. ID target_date mth_year mth_year_relative total_events total_events_A total_events_B 1 2/2010 3/2010 +1 3 1 2 1 2/2010 2/2010 0 5 4 1 1 2/2010 1/2010 -1 7 7 0 What I want is a dataset with one row per ID and the column heads to indicate both the relative mth/year and whether the column is the count of total events, type A events, or type B events, like this: ID target_date total_events_mth_3_pre total_events_mth_2_pre total_events_mth_1_pre total_events_mth_0 1 2/2010 7 5 total_events_mth_1_post total_events_mth_2_post total_events_mth_3_post total_events_A_mth_3_pre...etc through 3 total_events_B_mth_3 post Earlier, I only had the total events variable so I created a new variable based on relative mth_year to serve as the ID variable and transposed the variables to get what I wanted using this code: proc transpose data=have out=want; by ID target_date; var total_events; id fmtted_relative_mth_year; run; It worked perfectly, but now with the addition of the other two variables, I'm not sure how to transpose because I will have multiple values per by variable. Also note that I actually have 24 months pre and 24 months post, so if the solution involves typing out all the pre/post months by hand, that will start to get unwieldy. Any help is much appreciated.

Walternate · ‎07-22-2015

Hi, I have a dataset with two sets of month variables. Each set has months going from 10 months before a target date to 10 months after. ID Target_date mth_10_pre_a mth_9_pre_a mth_8_pre_a...mth_8_post_a mth_9_post_a mth_10_post_a mth_10_pre_b...etc. 1 2/2012 3 7 . 0 3 . . What I want is to use values from the first set of month variables (a) to change some of the values in the second set of month variables from missing to 0; eg, since mth_10_pre_a has a value (or a 0), then mth_10_pre_b should be changed from missing to 0 (if mth_10_pre_b already had a positive value, however, it should remain unchanged). I know that an array is theoretically the easiest way to do this, but since the numbers come in the middle of my variables, I'm not sure what's the most efficient way to go about it (ie, if there's a way to avoid changing all my variable names by hand). Any help is much appreciated.

Walternate · ‎07-22-2015

Hi, I have a dataset that is at the person/event level--each row represents one event for one person. It has ID variable, a target date, a variable I created to serve as the column heads after transposing, a month count, and a count variable. The month count indicates the number of months either before (in which case it will be negative) or after (positive) the target date that an event occurred. The variable I created corresponds with the month count variable: ID Target_date Mth_count Transpose_var Count 1 01/2010 -5 5_pre 5 1 01/2010 -3 3_pre 7 1 01/2010 2 2_post 13 1 01/2010 7 7_post 27 I have transposed the data such that the month counts are now columns (I kept the target date): proc transpose data=have out=want prefix=mth_; by ID target_date; var count; id transpose_var; run; This is the resulting dataset: ID Target_date mth_5_pre mth_3_pre mth_2_post mth_7_post 1 01/2010 5 7 13 27 The problem is that I need all the resulting month columns to be in order (ie, mth_10_pre, mth_9_pre...mth_0, mth_1_post, mth_2_post...mth_10_post). I know that the order is off because Person 1 has missing values for the other months, but I'm hoping there's a way to get SAS to output the columns in the order I want them. Any help is much appreciated.

Walternate · ‎07-15-2015

Here is the code to create Dataset1 (I used just one year of data): data dataset1; input id date1 mmddyy10. present_201001 present_201002 present201003 present201004 present201005 present201006 present_201007 present_201008 present201009 present201010 present201011 present201012; format date1 mmddyy10.; cards ; 1 01/07/2010 1 1 1 1 1 1 1 1 1 1 1 1 2 05/09/2010 0 0 0 0 1 1 1 1 1 1 1 1 3 04/12/2010 1 1 1 1 1 1 1 1 1 1 1 1 4 03/19/2010 1 1 1 1 1 1 1 1 1 0 0 0 5 12/07/2010 1 1 1 1 1 1 1 1 1 1 1 1 ; run; This is the output dataset when you run that code: id date1 present_201001 present_201002 present_201003 present_201004 present_201005 present_201006 present_201007 present_201008 present_201009 present_201010 present_201011 present_201012 1 1/7/2010 1 1 1 1 1 1 1 1 1 1 1 1 2 5/9/2010 0 0 0 0 1 1 1 1 1 1 1 1 3 4/12/2010 1 1 1 1 1 1 1 1 1 1 1 1 4 3/19/2010 1 1 1 1 1 1 1 1 1 0 0 0 5 12/7/2010 1 1 1 1 1 1 1 1 1 1 1 1 Here is the code for Dataset2: data dataset2; input id date2 mmddyy10. event_num; format date2 mmddyy10.; cards ; 1 1/9/2010 17 1 1/21/2010 23 1 2/4/2010 32 1 2/7/2010 12 1 4/5/2010 14 1 4/10/2010 1 1 4/21/2010 9 1 5/4/2010 91 1 5/9/2010 17 1 6/12/2010 33 2 5/4/2010 56 2 5/12/2010 74 2 5/30/2010 80 2 6/4/2010 11 2 6/20/2010 52 2 10/12/2010 63 2 10/29/2010 78 2 10/31/2010 33 3 1/24/2010 12 3 1/27/2010 10 3 2/3/2010 8 3 2/7/2010 9 3 2/17/2010 19 3 7/9/2010 22 3 7/14/2010 55 4 1/23/2010 29 4 1/27/2010 44 4 1/29/2010 35 4 3/5/2010 74 4 3/14/2010 92 4 7/2/2010 2 4 7/5/2010 13 4 8/9/2010 53 5 1/5/2010 72 5 1/23/2010 49 5 1/24/2010 14 5 9/5/2010 20 5 9/7/2010 11 5 9/12/2010 1 5 12/1/2010 4 5 12/31/2010 14 ; run; The output dataset: id date2 event_num 1 1/9/2010 7 1 1/21/2010 23 1 2/4/2010 2 1 2/7/2010 2 1 4/5/2010 4 1 4/10/2010 1 1 4/21/2010 9 1 5/4/2010 1 1 5/9/2010 7 1 6/12/2010 33 2 5/4/2010 6 2 5/12/2010 74 2 5/30/2010 80 2 6/4/2010 1 2 6/20/2010 52 2 10/12/2010 63 2 10/29/2010 78 2 10/31/2010 33 3 1/24/2010 12 3 1/27/2010 10 3 2/3/2010 3 3 2/17/2010 19 3 7/9/2010 2 3 7/14/2010 55 4 1/23/2010 29 4 1/27/2010 44 4 1/29/2010 35 4 3/5/2010 4 4 3/14/2010 92 4 7/2/2010 4 4 8/9/2010 3 5 1/5/2010 2 5 1/23/2010 49 5 1/24/2010 14 5 9/5/2010 0 5 9/7/2010 11 5 9/12/2010 1 5 12/1/2010 4 5 12/31/2010 14 The format of the dataset I want doesn't matter--the sums could be in columns or in rows, but the basic idea is: 1. Sums of event_num in each month relative to each person's date 1 (ie, -1 month, etc) 2. If a person does not have any events in a given month, they should have a missing value for the sum variable for that month UNLESS the present_201001 etc. vars from Dataset1 indicate that they were present in that month (=1), in which case, they should have a value of 0. So one possible final dataset would be like this: ID Pre_date1_12_mths Pre_date1_11_mths Pre_date1_10_mths Pre_date1_9_mths Pre_date1_8_mths Pre_date1_7_mths Pre_date1_6_mths Pre_date1_5_mths Pre_date1_4_mths Pre_date1_3_mths Pre_date1_2_mths Pre_date1_1_mths Same_mth_as_Date1 Post_date1_1_mths Post_date1_2_mths Post_date1_3_mths Post_date1_4_mths Post_date1_5_mths Post_date1_6_mths Post_date1_7_mths Post_date1_8_mths Post_date1_9_mths Post_date1_10_mths Post_date1_11_mths Post_date1_12_mths 1 . . . . . . . . . . . . 30 4 0 14 8 33 0 0 0 0 0 0 . 2 . . . . . . . . . . . . 160 53 0 0 0 174 0 0 . . . . . 3 . . . . . . . . . 22 22 0 0 0 0 57 0 0 0 0 0 . . . . 4 . . . . . . . . . . 108 0 96 0 0 0 4 3 0 . . . . . . 5 65 0 0 0 0 0 0 0 0 12 0 0 18 . . . . . . . . . . . . But it could also be the same numbers structured in a different way, like this: ID Relative_mth total 1 -12 . etc.

Walternate · ‎07-14-2015

Hi, I have two datasets. Dataset 1 is at the person level and has ID, a date variable, and a series of variables named for year/month combinations. A value of 0 for a year/month variable means that the person was absent in that year/month; a value of 1 means that they were present: ID Date1 Jan2010 Feb2010....etc through Mar2015 1 1/4/12 0 1 0 2 5/9/12 1 0 0 3 4/12/13 1 1 1 Dataset 2 is at the person/date level; that is, each row is one person on one date. Each row also has a number associated with it (number of events): ID Date2 Num_events 1 2/3/2014 5 1 10/12/2014 1 1 11/5/2014 2 2 1/1/2012 2 2 5/7/2013 3 I have joined the datasets on ID, and I need two different things: 1. I need every person's total num_events by month/year combo--however, this needs to be cross-referenced with the year/month variables, because if they have a value of 1 for that year/month but no events, they should have a value of 0 rather than missing. For example, ID 1 was present in Feb 2010 because they have Feb2010=1. In Dataset 2, they have no events in Feb 2010 (because they have no record for Feb 2010). Therefore, ID 1's total events for Feb 2010 should=0. However, they were absent in Jan 2010 (Jan2010 var=0), so their event total for Jan 2010 will be null, not 0. 2. Even more complicated--ultimately what I want is to have everyone's event total relative to their own Date1 (the date variable in Dataset 1). That is, the event total for ID 1 in Dec 2011 would be -1 month from their Date1 var, but for ID 2 it would be -5 months from their Date1 var. Basically, I can't figure out how to reconcile all of these elements. I was able to create a dataset of total events by relative month by calculating a new variable which counted the number of months between Date1 and Date2 (mth_diff), then doing proc sql; create table mthly_sums as select ID, mth_diff, sum(num_events) as tot from have group by ID, mth_diff; quit; But that doesn't incorporate the mth/year vars and the fact that people who were present but had no events should be 0 and not missing.

Walternate · ‎07-14-2015

Hi, I have a dataset at the person/month/year level--ie, each row is one person for one mth/year combination. People occur multiple times in the dataset (but a new row would be a different mth/year). There are two date columns. date_col_1 is at the person level--ie, for each of the person's rows, date_col_1 will always be the same. date_col_2 reflects the mth/year that the event took place (format is yyyymm). Total is a sum variable reflecting the # of times an event happened within each mth/year for each person. Example data: ID date_col_1 date_col_2 total 1 1/3/2012 201203 5 1 1/3/2012 201204 7 1 1/3/2012 201205 3 2 10/5/2013 201201 1 2 10/5/2013 201202 12 3 3/4/2012 201301 11 3 3/4/2012 201305 4 3 3/4/2012 201401 5 3 3/4/2012 201403 10 What I want is a new variable that gives date_col_2 relative to date_col_1--that is, the number of months that date_col_2 occurs before or after date_col_1. For example, in the first row it would be +2 months because date_col_2 is two months after date_col_1; in the first row for ID 2 it would be -21 because date_col_2 is 21 months prior to date_col_1. Any help is much appreciated.

Walternate · ‎07-06-2015

You're right--I invented those examples, and now that I look at it more closely, the values ending in .0 or .5 were actually converted correctly. Changing my length statement to 8. fixed the issue.

Walternate · ‎07-06-2015

Hi, I have a dataset with two character variables (composed entirely of numbers). Each person will have a value for Char_var1 or Char_var2, but not both. I created a variable that basically combines the values of Char_var1 and Char_var2, so everyone will have a value for the combined variable. I wanted the combination variable to be numeric so I added a length statement: data want; length combined_var 3.; set have; if char_var1 ne " " then combined_var=char_var1; else if char_var1=" " then combined_var=char_var2; run; The problem is that in the resulting dataset, the combined_var has added several digits to each value: Char_var1 Char_var2 Combined_var 1.5 1.51493 6.3 6.32139 7.0 7.01493 I'm not sure where the digits came from--the source data has only one digit after the decimal point for Char_var1 and Char_var2. Any help is much appreciated.

Walternate · ‎07-06-2015

Hi, I have a dataset in which I've created an age variable using a date of birth variable and an "age as of" variable (which I created as well), like this: /*Calculate age from DOB*/ data want_age; set have; ageasof='01NOV2012'D; run; DATA create_age; set want_age; age=floor(yrdif(datepart(dob), ageasof, age)); run; The code runs fine and seems to calculate age correctly; however, I get a message after the second step saying that Numeric values have been converted to character values. I want age to be a numeric variable, so this is problematic. However, when I run a proc contents, it says age is already numeric, and when I go to the next step which uses values of age to create age categories, I treat age as numeric and it works fine. I'm confused about why I'm getting the message re: age being converted to character values when that doesn't actually seem to be the case. Any help is much appreciated.

Walternate · ‎06-26-2015

My log just says that the which_dates table was created. NOTE: Table WORK.which_dates created, with 342 rows and 1 columns. 19 quit; NOTE: PROCEDURE SQL used (Total process time): real time 0.01 seconds cpu time 0.01 seconds I tried the = and that didn't make a difference.

Walternate · ‎06-26-2015

Hi, I am creating a date variable in my dataset out of two other date variables, DateVar1 and DateVar2. If the person has a value for DateVar1, the new date var will take that value; if they do not, the new date var will take the value for DateVar2, like this: data want; format new_date_var date9.; set have; if DateVar1 ne . then NewDateVar=DateVar1; else if DateVar1=. then NewDateVar=DateVar2; run; Now I'm trying to pull out the dates for which my categorical variable, CategVar, has a value of A, so I can see which dates fall into that category. I tried this: proc sql; create table which_dates as select new_date_var from want where CategVar='A'; quit; It runs fine, but the date variable is just filled with asterisks rather than values, even when I insert a formatting statement: proc sql; create table which_dates as select new_date_var format date9. from want where CategVar='A'; quit; Any help is much appreciated.

Online Status	Offline
Date Last Visited	‎02-27-2025 04:00 PM

ODS Excel - building within-document hyperlink using a numeric row var...

Reading in SAS program and not seeing the formatting the way it shoul...

Weird characters messing up directory/file name macros

Using libname to create directories when some directories not represen...

How to build an output indicating which numbers in a range are not pre...

Re: Possible to remove carriage returns from a string and leave the re...

Re: Pattern matching to two different patterns

Pattern matching to two different patterns

Re: Possible to remove carriage returns from a string and leave the re...

Possible to remove carriage returns from a string and leave the rest o...

Re: Parsing a character string based on format

Re: Residuals in logistic regression

Merge step overwriting shared vars?

Transposing multiple variables

Re: Missing values in infile statement

Filling variable from another row with same ID

Join on first and last name

Overwriting records from one dataset with records from another

proc sql like operator filtering missing values

Transposing multiple variables

Filling one set of vars from another set of vars

Keeping output columns in order with proc transpose missing values

Re: Sum variable by relative dates

Sum variable by relative dates

# of months between two date vars

Re: Numeric variable adding extra digits

Numeric variable adding extra digits

Age variable char vs numeric

Re: Date variable formatting in proc sql

Date variable formatting in proc sql