About geneshackman

geneshackman · ‎08-04-2022

Okay, I apologize. It's a csv file, not an xlsx file. Really my bad. End of day and I wasn't careful. Sorry.

geneshackman · ‎08-04-2022

Thanks Reeza. I tried that and still got an error. I must have a typo that I just can't find. 😞 153 proc import out = pzip&start.&end 154 datafile = 154! "\\dohfile02\phig\PHIGDATA\Data\perinatal_zips\perinzip_&start4._&end4..xlsx" 155 DBMS=XLSX REPLACE; 156 getnames = yes; 157 SHEET="PerinZIP_&start4._&end4"; 158 options MSGLEVEL=i; 159 run; ERROR: Physical file does not exist, \\dohfile02\phig\PHIGDATA\Data\perinatal_zips\perinzip_2018_2020.

geneshackman · ‎08-04-2022

Hi all. I'm hoping this is a fairly simple question, I can't get the syntax quite right. Or else I'm making a simple typo that I just can't see. %let start=18; *first year; %let end=%eval(&start+2); *end year; %let start4=2018; %let end4 = %eval(&start4+2); proc import out = pzip&start.&end datafile = "\\dohfile02\phig\PHIGDATA\Data\perinatal_zips\perinzip_&start4._&end4.xlsx" DBMS=XLSX REPLACE; getnames = yes; SHEET="PerinZIP_&start4._&end4"; options MSGLEVEL=i; run; The exel file is named: PerinZIP_2018_2020.xlsx and the sheet I want is: PerinZIP_2018_2020 In this folder \\dohfile02\phig\PHIGDATA\Data\perinatal_zips the error i get is generally this, "Physical file does not exist" 316 proc import out = pzip&start.&end 317 datafile = 317! "\\dohfile02\phig\PHIGDATA\Data\perinatal_zips\perinzip_&start4._&end4.xlsx" 318 DBMS=XLSX REPLACE; 319 getnames = yes; 320 SHEET="PerinZIP_&start4._&end4"; 321 options MSGLEVEL=i; 322 run; ERROR: Physical file does not exist, \\dohfile02\phig\PHIGDATA\Data\perinatal_zips\perinzip_2018_2020xlsx. NOTE: The SAS System stopped processing this step because of errors.

geneshackman · ‎07-31-2022

Thanks, that was it. I closed, and re-opened and re-ran, and it worked fine. I -did- have an earlier error with a quote, but i fixed it before i ran this. Anyway, when i closed this sas session, and re-opened and re-ran, no problem. Thanks again.

geneshackman · ‎07-31-2022

I get this error. I looked it up on the web and I'm not sure solutions on the web apply to my code data temptotal; set tempa tempb; if county eq "New York State" then delete; if race = "County Total" and county = "State Total" then delete; if county eq "State Total" then county_code = 36000; run; here is the log data temptotal; 1074 set tempa tempb; 1075 if county eq "New York State" ------------------------- 49 1075! then delete; 1076 if race = "County Total" and county = "State Total" then delete; ---------------- ---------------------------- 49 49 NOTE 49-169: The meaning of an identifier after a quoted string might change in a future SAS release. Inserting white space between a quoted string and the succeeding identifier is recommended. 1077 if county eq "State Total" then county_code = 36000; 1078 run; running on sas 9.4, on windows. I don't have any characters after quotes. As far as I can tell, the end of every quoted phrase is followed by a space. Do i have an error in punctuation?

geneshackman · ‎07-31-2022

Thanks very much! That was it.

geneshackman · ‎07-31-2022

Just to mention, i tried this so far proc sql; create table tempb as select *, sum(population) from tempa group by race order by race; quit; but it returns all the counties, by race, so it's returning the same data. What am i doing wrong?

geneshackman · ‎07-31-2022

Hi all. This should be simple, but I can't quite get it to work, and what I found on web searching hasn't helped exactly, perhaps because i don't understand sql well enough. Here is part of the data. Each county has population by race/ethnicity. I want to get state population by race/ethnicity, summing across all counties. Shouldn't this be fairly simple? CountyCode Race Population County 36000 State Total 58344005 New York State 36001 County Total 915097 Albany 36001 Hispanic or Latino 58094 Albany 36001 NH American Indian or Alaska Native 1370 Albany 36001 NH Asian and Pacific Islander 63194 Albany 36001 NH Black or African American 116750 Albany 36001 NH More than one race 22848 Albany 36001 NH White 652841 Albany 36003 County Total 137788 Allegany 36003 Hispanic or Latino 2401 Allegany 36003 NH American Indian or Alaska Native 295 Allegany 36003 NH Asian and Pacific Islander 1739 Allegany 36003 NH Black or African American 1709 Allegany 36003 NH More than one race 1632 Allegany 36003 NH White 130012 Allegany 36005 County Total 4251645 Bronx 36005 Hispanic or Latino 2390496 Bronx 36005 NH American Indian or Alaska Native 11814 Bronx 36005 NH Asian and Pacific Islander 176974 Bronx 36005 NH Black or African American 1242803 Bronx 36005 NH More than one race 42322 Bronx 36005 NH White 387236 Bronx Thanks Gene

geneshackman · ‎07-25-2022

This seems to be the way. Thanks.

geneshackman · ‎07-22-2022

Hi all. I have a data set like this County Race Data Albany White 3 Albany Black 2 Albany Asian 5 Albany Pacific Islander 3 Allegany White 1 Allegany Black 6 Allegany Asian 2 Allegany Pacific Islander 4 I want to add together the lines for "Asian" and "Pacific Islander" but leave the rest of the lines as they are. So, the resulting race categories would be White, Black and Asian/Pacific Islander, with the numbers for those last two combined. Any simple way to do this? Thanks Gene

geneshackman · ‎07-22-2022

For separating out the county name and ID, Ballardw's method worked for me countyname = tranwrd(scan(county,1,','),'County',''); I'll look at the other part now, collapsing two groups. Thanks

geneshackman · ‎07-21-2022

Ballardw, your solution to splitting the county name works. I'll try the remaining part tomorrow. Reeza your part almost works. I'm not clear if I indicated that I didn't want the word "County" in the output, just the name of the county. Thanks both.

geneshackman · ‎07-21-2022

Thanks Reeza, I forgot. Yes, variable names would be Ethnicity, County, Race, Total, Age65Plus

geneshackman · ‎07-21-2022

Hi all I hope everyone is healthy! Here is part of my data set Not Hispanic or Latino Albany County, NY (36001) American Indian or Alaska Native 1,370 300 Not Hispanic or Latino Albany County, NY (36001) Asian 62,742 4,520 Not Hispanic or Latino Albany County, NY (36001) Black or African American 116,750 11,993 Not Hispanic or Latino Albany County, NY (36001) Native Hawaiian or Other Pacific Islander 452 12 Not Hispanic or Latino Albany County, NY (36001) White 652,841 137,877 Not Hispanic or Latino Albany County, NY (36001) More than one race 22,848 1,135 Not Hispanic or Latino Albany County, NY (36001) Total ##### ##### Not Hispanic or Latino Allegany County, NY (36003) American Indian or Alaska Native 295 62 Not Hispanic or Latino Allegany County, NY (36003) Asian 1,701 115 Not Hispanic or Latino Allegany County, NY (36003) Black or African American 1,709 79 Not Hispanic or Latino Allegany County, NY (36003) Native Hawaiian or Other Pacific Islander 38 3 Not Hispanic or Latino Allegany County, NY (36003) White 130,012 26,328 Not Hispanic or Latino Allegany County, NY (36003) More than one race 1,632 121 Not Hispanic or Latino Allegany County, NY (36003) Total ##### 26,708 I want to make this part "Albany County, NY (countynumber") into two variables county name (e.g. "Albany") and county number (e.g. 36001) Is that possible? Second, one row is "Native Hawaiian or Other Pacific Islander" and another row is "Asian". I'd like to add them together to make a row "Asian/Pacific Islander", and then remove the two component rows. Thanks Gene

geneshackman · ‎05-24-2022

In this case, i can remove * from the numbers column, because i have another column explaining the meaning. So i don't need *.

Online Status	Offline
Date Last Visited	‎04-10-2024 06:21 PM

Re: summing two rows

Re: summing two rows

summing two rows

Re: adding row to data set, mostly duplicate but some different

Re: adding row to data set, mostly duplicate but some different

Re: adding row to data set, mostly duplicate but some different

Re: adding row to data set, mostly duplicate but some different

Re: adding row to data set, mostly duplicate but some different

adding row to data set, mostly duplicate but some different

Re: wide to long with first set of columns stay the same

Re: summing two rows

Re: summing two rows

Re: adding row to data set, mostly duplicate but some different

Re: wide to long with first set of columns stay the same

Re: adding leading zeros and changing to string

Re: import xlsx file using macros about years

Re: import xlsx file using macros about years

import xlsx file using macros about years

Re: NOTE 49-169: The meaning of an identifier after a quoted string mi...

NOTE 49-169: The meaning of an identifier after a quoted string might ...

Re: summing across units, vertically, using sql

Re: summing across units, vertically, using sql

summing across units, vertically, using sql

Re: adding up -some- lines in a data set

adding up -some- lines in a data set

Re: seperating variable text, and creating new line

Re: seperating variable text, and creating new line

Re: seperating variable text, and creating new line

seperating variable text, and creating new line

Re: Removing * from numbers column

SAS Analytics Explorers