topic Re: How can I get rid of unnecessary rows when I import an xlsx file to a temporary sas dataset? in SAS Programming

How can I get rid of unnecessary rows when I import an xlsx file to a temporary sas dataset?

dseals23 — Sat, 09 Dec 2017 18:21:57 GMT

Hello All,

My first question is how can I get rid of unnecessary rows when I import an xlsx file to a temporary sas version 9 dataset? Here is my xlsx file and here is what sas produces from that file in a screenshot. The other question is can I create a permanent dataset when I import an xlsx file or do I have to create a procedure to do that?

25 years and over	55,270	56,284	947	969
16 to 24 years	4,314	4,430	450	486
25 years and over	44,020	44,731	761	784
	.	.	.	.
	.	.	.	.
	.	.	.	.
	.	.	.	.
	.	.	.	.
	.	.	.	.
	.	.	.	.
	.	.	.	.
	.	.	.	.
	.	.	.	.
	.	.	.	.
	.	.	.	.
	.	.	.	.
	.	.	.	.
	.	.	.	.
	.	.	.	.
	.	.	.	.
	.	.	.	.
	.	.	.	.
	.	.	.	.
	.	.	.	.
	.	.	.	.
	.	.	.	.
	.	.	.	.
	.	.	.	.
	.	.	.	.
	.	.	.	.
	.	.	.	.
	.	.	.	.
	.	.	.	.

Re: How can I get rid of unnecessary rows when I import an xlsx file to a temporary sas dataset?

Kurt_Bremser — Sat, 09 Dec 2017 18:33:18 GMT

You could add a where= dataset option to the dataset in the proc import statement.

Or you could save to a csv file from Excel and take care of unnecessary data in the data step that reads the csv. Which is the preferred way.

Re: How can I get rid of unnecessary rows when I import an xlsx file to a temporary sas dataset?

dseals23 — Sat, 09 Dec 2017 21:55:11 GMT

@Kurt_Bremser, I have two different data excel files named EthnicitySexInfo and SexAgeInfo as excel files but I saved them as csv files currently. I didn't have to use the snippet tool feature to import csv files; however, I just put them directly into the sasuniversity folder which then would automatically get transferred to the 68380_ example folder that I want. To make a long story short, the files are both csv files now. But then I went to import one of the files using this code, it says the file could not be found. I am just testing it out to see if it would output any data in that csv file.I understand what the error message means but there is a file inside of that directory. So that doesn't make sense to me. Here is import file code for the SexAgeInfo csv file:

Another comment that I wanted to make is if I put all these files inside of 68380_example folder using the file explorer path that leads to the 68380_example folder, it does successfully transfer those same files to the SAS Studio environment which also has all the same directories.

/** FOR CSV Files uploaded from Windows **/
FILENAME CSV "myfolders/folders/68380_example/SexAgeInfo.csv" TERMSTR=CRLF;

/** Import the CSV file. **/
PROC IMPORT DATAFILE=CSV
OUT=WORK.SexAgeInfo
DBMS=CSV
REPLACE;
RUN;
/** Print the results. **/
PROC PRINT DATA=WORK.SexAgeInfo; RUN;
/** Unassign the file reference. **/
FILENAME CSV;

Re: How can I get rid of unnecessary rows when I import an xlsx file to a temporary sas dataset?

SASKiwi — Sat, 09 Dec 2017 22:45:49 GMT

You could also try doing a row delete below your data lines in Excel. Sometimes rows aren't actually "empty" even though they look blank.

Re: How can I get rid of unnecessary rows when I import an xlsx file to a temporary sas dataset?

Kurt_Bremser — Sun, 10 Dec 2017 07:11:26 GMT

Always start your file paths with a slash, so the system starts to look from the root directory up. Without the leading slash the system starts to look up from the current working directory of the SAS process, and ends up in a nowhere. You can see this in the log.

Once your import from csv works, you will find data step code created by proc import in the log; copy that, and add your selection logic to it.

Re: How can I get rid of unnecessary rows when I import an xlsx file to a temporary sas dataset?

dseals23 — Sun, 10 Dec 2017 21:28:39 GMT

@Kurt_Bremser @SASKiwi Okay, when I import both of my excel files as csv files and press the run on both of the code files, it does output all the information with commas and all. But now when I create a permanent dataset for the SexAgeInfo, it doesn't output the exact format as my original data in the csv files. How can I fix this? Should I use format keyword when creating a permanent data? Also, how will I solve the problem of getting the men and women into my dataset for each year so a user can know which gender is associated with the age ranges in my SexAgeInfo dataset? Here is my generated output for each dataset named EthnicitySexInfo and SexAgeInfo:

More Info: You can take a look back at the original dataset before I split up it between the two datasets now (this is so you know how it originally looks -if you want to https://communities.sas.com/t5/Base-SAS-Programming/Is-SAS-dataset-Okay-to-work-with/m-p/413753#M101305)

EthnicitySexInfo datset

Obs Characteristic VAR2 VAR3 VAR4 VAR5
1 Men 48,746 49,310 920 942
2 Women 36,698 37,163 743 766
3 Men 6,445 6,728 680 718
4 Women 7,142 7,235 615 641
5 Men 3,684 3,888 1,129 1,151
6 Women 2,954 3,142 877 902
7 Men 11,142 11,666 631 663
8 Women 7,168 7,284 566 586

SexAgeInfo dataset:

Obs Characteristic VAR2 VAR3 VAR4 VAR5
1 16 to 24 years 5,476 5,646 510 512
2 25 years and over 55,270 56,284 947 969
3 16 to 24 years 4,314 4,430 450 486
4 25 years and over 44,020 44,731 761 784

Re: How can I get rid of unnecessary rows when I import an xlsx file to a temporary sas dataset?

dseals23 — Sun, 10 Dec 2017 22:17:01 GMT

@Kurt_Bremser Never mind about the part of how to get male and female into the SexAgeInfo dataset. I just added another column into the csv file and that works, but I am still stuck on how to get all the number to be with a comma and most of all these numbers are still not showing up in the sas permanent dataset.

Re: How can I get rid of unnecessary rows when I import an xlsx file to a temporary sas dataset?

Kurt_Bremser — Mon, 11 Dec 2017 08:00:33 GMT

This is just a very simple data step where you assign proper informats and display formats, and check for content:

data sexageinfo;
infile "$HOME/sascommunity/SexAgeInfo.csv" dsd dlm=',' truncover;
length
  characteristic $20
  var2-var5 8
;
informat var2-var5 comma10.;
format var2-var5 comma10.;
input
  characteristic
  var2
  var3
  var4
  var5
;
if not missing(characteristic);
run;

Re: How can I get rid of unnecessary rows when I import an xlsx file to a temporary sas dataset?

ballardw — Mon, 11 Dec 2017 18:01:00 GMT

@dseals23 wrote:

@Kurt_Bremser Never mind about the part of how to get male and female into the SexAgeInfo dataset. I just added another column into the csv file and that works, but I am still stuck on how to get all the number to be with a comma and most of all these numbers are still not showing up in the sas permanent dataset.

It may help to post the generated SAS data step that you are modifying. Indicate which variables should be numeric.

INFORMAT statements tell SAS how to read a value. If the value is desired to be numeric and has commas in the value then use an comma. informat. If you want to see the commas in a numeric value then you associate a format that uses commas to display numeric. That would be set with a Format statement.

If SAS is reading a value that is supposed to be read with a numeric format such as BEST4. or similar then values with comma's are treated as character. You would see messages in the log about invalid values for variable VAR in that case.

Re: How can I get rid of unnecessary rows when I import an xlsx file to a temporary sas dataset?

Phil_NZ — Mon, 15 Feb 2021 20:13:53 GMT

Hi @SASKiwi

You definitely saved my date, I am wondering why some of my countries importing from Excel and the first observation after sorting is blank. After applying your approach, it works well.

Warmest regards.