About wernie

wernie · ‎04-28-2020

I need to extract words before a delimiter (comma) and can't figure out how to do it. In some cases it's one word and in other cases it's more than one word. For example, I have: Clay County, AL Clark County, AL La Paz County, AZ Santa Cruz County, AZ So for each of those, I want to extract all of the words before the comma. Thanks!

wernie · ‎04-24-2020

Oops, here is the data as data step code! data work.have; infile datalines; input Action $ State $ Date1 :mmddyy10. Date2 :mmddyy10.; format Date1 mmddyy10. Date2 mmddyy10.; datalines; Walk AK 04/01/2020 04/09/2020 Run AL 03/30/2020 04/03/2020 Walk AR 03/26/2020 04/02/2020 Walk AZ 04/03/2020 04/08/2020 Sit CA 03/28/2020 04/01/2020 Run CO 04/01/2020 04/09/2020 ; run; I don't have the code for the data that I want (obviously), but I put a screenshot of what that looks like so it's PDF instead of Excel to give an idea of what I'm trying to get. Thanks!

wernie · ‎04-24-2020

Hi all, I attached an example dataset similar to what I have and what I want. Someone will be sending me data, so I want to make it as easy as possible to process without having to manually go in and change dates, etc. I'll have a written out 'action' and then each state (the example dataset just has a few states). Then there will be two sets of dates. What I want to do is create a daily dataset starting at some date that I specify and have each state listed for each date (I learned how to do just this part yesterday) to a specified end date. What I'm not sure how to do is then go through each state and say if it's between date1 and date2 then list out 1 (or whatever the action value should be) and if it's not between those dates, then make it a 0. I have this shown in my example 'want' dataset in the spreadsheet (where 0=no action, 1=walk, 2=run, 3=sit). I can't think of how to do this with macros to output everything and not overwrite each state as I want a complete dataset. I'm not sure how often I'll be getting an updated spreadsheet to run through, so anything to make updating it easiest is best. Thanks!

wernie · ‎04-23-2020

I am starting from scratch and want to create a dataset that has daily dates (e.g., starting 03/15/2020 through something that would be today's date whenever it is run) for each state (see below for example). I'll then use that as my base to fill in my other conditional variables. How can I do this? Date State 03/15/2020 AK 03/15/2020 AL ............. .... 03/15/2020 WY 03/16/2020 AK 03/16/2020 AL and so on through 04/23/2020 (or whatever the current date is for the day it is being run) Thanks!

wernie · ‎03-30-2020

@LeonidBatkhan thanks so much! That worked well.

wernie · ‎03-27-2020

@novinosrin ugh, yes, there was much more to that post. I already tried posting it twice and something doesn't seem to be working properly. Strange because it showed up for me initially, but now it's blank. Will try again in a different browser.

wernie · ‎03-27-2020

Trying to post this for the third time. Hoping the text shows... I'm trying to add the values from one variable for one observation to another observation. I have the following: FIPS5 char_state char_county yr num_days 51015 51 015 2010 2 51017 51 017 2010 1 51019 51 019 2010 3 51021 51 021 2010 5 .... .. ... .... . (continued with all FIPS codes) 51515 51 515 2010 2 I want to add the num_days value from 51515 (2) to the num_days value for 51019 (3) and get the new total of 5 assigned to the 51019 row and delete 51515, so I'd end up with: FIPS5 char_state char_county yr num_days 51015 51 015 2010 2 51017 51 017 2010 1 51019 51 019 2010 5 (new total) 51021 51 021 2010 5 .... .. ... ... . (continued with all FIPS codes) 51515 deleted from the table. Thanks!

wernie · ‎02-24-2020

I have a dataset that has census tract fips codes, but the leading 0 is getting cut off. I was able to put a leading 0 in front of the county fips code using substr and length, but the same thing is not working for census tract. I keep getting errors. Basically, I have ct fips that look like this: 1001020100 1001020200 24006840030 24007850040 And I want it to look like this (notice that the last two remain the same as they already have 11 digits); 01001020100 01001020200 24006840030 24007850040 I want to leave these as character variables. In the code, I put data want; set have; ct = substr(ctfips, length(ctfips)-10, 11); drop ctfips; rename ct = ctfips; run; Not sure if that's what I need to be doing, but it's not giving the leading zero for a total length of 11. Thanks!

wernie · ‎01-31-2020

Edited original post. I just need to be able to expand the counties in the dataset to have all counties included (could merge with some county reference file).

wernie · ‎01-29-2020

I have a file where I need to expand to include all counties. The first file that has the data I need (start and end month and days) does NOT have all counties. It looks like this: STATE COUNTY FIPS5 START_MONTH START_DAY END_MONTH END_DAY 01 01 3 1 10 31 01 073 01073 1 1 12 31 02 02 4 1 10 30 02 090 02090 1 1 12 31 04 04 1 1 12 31 04 005 04005 1 1 12 31 04 007 04007 1 1 12 31 I need to merge this with a file that has all state and county codes to fill in values for ALL counties. I just want to be able to expand the counties in the new file and wherever the county value is blank, I want to apply the start_month, start_day, end_month, and end_day values to those expanded counties, while leaving the values for the rows where county DOES have a value in the data file. Essentially, I want to expand the new file to look something like this (dots are just to continue rows up to observation where something would be different): STATE COUNTY FIPS5 START_MONTH START_DAY END_MONTH END_DAY 01 001 01001 3 1 10 31 01 003 01003 3 1 10 31 01 005 01005 3 1 10 31 01 007 01007 3 1 10 31 … …….. . . .. .. 01 073 01073 1 1 12 31 02 013 02013 4 1 10 30 02 016 02016 4 1 10 30 … …….. . . .. .. 02 090 02090 1 1 12 31 I hope this makes sense. How can I expand to fill in all county values? I'm thinking to take a county reference file and merge by county and that might fill in the counties where they aren't listed, but I still need to retain the counties that have a different start and end month and day. Thank you!

wernie · ‎01-08-2020

Hi all, I have two datasets that I want to merge together. One has values where they were obtained from a monitor (so it's only for a subset of counties within a state), the other has values where they were obtained from modeling (so it's for all counties within a state). I want to be able to create one final dataset where it has all counties where it uses the monitoring value if there's a value for that in the monitoring dataset and it uses the model value if there's no value for that in the monitoring dataset. An example of the datasets is below as well as what the final dataset should look like. Model_Flag (0 = monitor, 1 = model). Thank you! Monitor Year State County State_County Pop Value1 Value2 Model_Flag 2014 36 001 36001 274658 0 0 0 2014 36 005 36005 837456 1 7261 0 2014 36 013 36013 1846372 3 958754 0 2014 36 041 36041 85643 2 54678 0 Model Year State County State_County Pop Value1 Value2 Model_Flag 2014 36 001 36001 274658 0 0 1 2014 36 007 36007 726785 4 63932 1 2014 36 017 36017 529676 2 13462 1 Merged dataset (keeping mon value where available for county and filling in modeled value where not available in monitored dataset) Year State County State_County Pop Value1 Value2 Model_Flag 2014 36 001 36001 274658 0 0 0 2014 36 005 36005 837456 1 7261 0 2014 36 007 36007 726785 4 63932 1 2014 36 013 36013 1846372 3 958754 0 2014 36 017 36017 529676 2 13462 1 2014 36 041 36041 85643 2 54678 0

wernie · ‎10-07-2019

I’m just trying to collapse the age groups to get <50, 50-65, 65+ and get the population total for those groups. The GEO_ID a unique ID so each GEO_ID needs to have the age groups collapsed and populations summed.

wernie · ‎10-07-2019

Hi, I have population data for different age groups and I want to collapse the age groups to <50, 50-65, and 65+ to get the total population for each of those age groups. I have: GEO_ID Age_Group Pop 1 1 50 1 2 80 1 3 90 1 4 150 1 5 180 1 6 205 1 7 258 1 8 375 1 9 383 1 10 865 So I basically want to get the following: GEO_ID Age_Group Pop 1 1 (new value for <50) 5004 1 2 (new value for 50-65) 6493 1 3 (new value for 65+) 3026 Thanks!

wernie · ‎09-16-2019

I have a dataset that has info for different census tracts. It does not currently have any age group information, so I want to expand to include 19 age groups (meaning each census tract row that I currently have would be replicated to have info for all 19 age groups). I'm not sure how to go about doing this. Thank you! I currently have something that looks like this: GEO_ID State Tract 12001000200 12 000200 12001000300 12 000300 12001000400 12 000400 And I want to get it to look like this: GEO_ID State Tract Age_Group 12001000200 12 000200 1 12001000200 12 000200 2 12001000200 12 000200 3 12001000200 12 000200 4 12001000200 12 000200 5 12001000200 12 000200 6 12001000200 12 000200 7 12001000200 12 000200 8 12001000200 12 000200 9 12001000200 12 000200 10 12001000200 12 000200 11 12001000200 12 000200 12 12001000200 12 000200 13 12001000200 12 000200 14 12001000200 12 000200 15 12001000200 12 000200 16 12001000200 12 000200 17 12001000200 12 000200 18 12001000200 12 000200 19 12001000300 12 000300 1 12001000300 12 000300 2 12001000300 12 000300 3 12001000300 12 000300 4 12001000300 12 000300 5 12001000300 12 000300 6 12001000300 12 000300 7 12001000300 12 000300 8 12001000300 12 000300 9 12001000300 12 000300 10 12001000300 12 000300 11 12001000300 12 000300 12 12001000300 12 000300 13 12001000300 12 000300 14 12001000300 12 000300 15 12001000300 12 000300 16 12001000300 12 000300 17 12001000300 12 000300 18 12001000300 12 000300 19

wernie · ‎08-15-2019

Thanks everyone! I got it sorted out! The log had a warning about 'Multiple lengths were specified for the variable Temp_Agg by input data set(s). This can cause truncation of data.' I went to an earlier macro step and set it so all datasets would have the same length for the Temp_Agg variable and that then allowed it to apply that format properly.

Online Status	Offline
Date Last Visited	‎04-28-2020 03:20 PM

Extracting word(s) before comma delimiter

Re: Transforming a dataset and calculating variables with dates

Transforming a dataset and calculating variables with dates

How to create new dataset with daily dates for each state?

Re: How to add value from variable of one observation to another obser...

Re: How to add value from variable of one observation to another obser...

How to add value from variable of one observation to another observati...

Add leading 0 to census tract value (character var)

Re: Need help merging two files to fill in values

Need help merging to expand dataset

Re: Extracting word(s) before comma delimiter

Re: How to create new dataset with daily dates for each state?

Re: How to create new dataset with daily dates for each state?

Re: How to add value from variable of one observation to another obser...

Re: How to add value from variable of one observation to another obser...

Re: How to add value from variable of one observation to another obser...

Extracting word(s) before comma delimiter

Re: Transforming a dataset and calculating variables with dates

Transforming a dataset and calculating variables with dates

How to create new dataset with daily dates for each state?

Re: How to add value from variable of one observation to another obser...

Re: How to add value from variable of one observation to another obser...

How to add value from variable of one observation to another observati...

Add leading 0 to census tract value (character var)

Re: Need help merging two files to fill in values

Need help merging to expand dataset

Select which observations to include in merge

Re: How to sum values in a column and recode the values?

How to sum values in a column and recode the values?

Expand dataset to include age groups

Re: Losing character format when setting multiple datasets?