topic Re: How do you force a column variable to be character, not numeric? in SAS Procedures

How do you force a column variable to be character, not numeric?

Afor910327 — Mon, 29 Apr 2019 19:58:23 GMT

How do I force the itemCode variable to be character, not numeric?

PROC IMPORT DATAFILE= "&drive\ACPSA_Items\Item_Partials\&prodyr\ACPSA_Items_Partials_56.xlsx"
OUT= tempsas.itemACPSA_56
DBMS=xlsx REPLACE;
SHEET="Item_Partial_List_56";
GETNAMES=YES;

run;

2134 PROC IMPORT DATAFILE= "&drive\ACPSA_Items\Item_Partials\&prodyr\ACPSA_Items_Partials_56.xlsx"
SYMBOLGEN: Macro variable DRIVE resolves to j:\IEA Products\ACPSA
SYMBOLGEN: Macro variable PRODYR resolves to 2019
2135 OUT= tempsas.itemACPSA_56
2136 DBMS=xlsx REPLACE;
2137 SHEET="Item_Partial_List_56";
2138 GETNAMES=YES;
2139
2140 run;

NOTE: The import data set has 1 observations and 28 variables.
NOTE: TEMPSAS.ITEMACPSA_56 data set was successfully created.
NOTE: PROCEDURE IMPORT used (Total process time):
real time 0.04 seconds
cpu time 0.03 seconds

Re: How do you force a column variable to be character, not numeric?

Reeza — Mon, 29 Apr 2019 20:57:17 GMT

PUT() converts a variable to character. You cannot control types when reading in excel, because Excel does not enforce types on the field.

data want;
set sashelp.class;

age_char = put(age, 8. -l);

run;

@Afor910327 wrote:

How do I force the itemCode variable to be character, not numeric?

PROC IMPORT DATAFILE= "&drive\ACPSA_Items\Item_Partials\&prodyr\ACPSA_Items_Partials_56.xlsx"
OUT= tempsas.itemACPSA_56
DBMS=xlsx REPLACE;
SHEET="Item_Partial_List_56";
GETNAMES=YES;

run;

2134 PROC IMPORT DATAFILE= "&drive\ACPSA_Items\Item_Partials\&prodyr\ACPSA_Items_Partials_56.xlsx"
SYMBOLGEN: Macro variable DRIVE resolves to j:\IEA Products\ACPSA
SYMBOLGEN: Macro variable PRODYR resolves to 2019
2135 OUT= tempsas.itemACPSA_56
2136 DBMS=xlsx REPLACE;
2137 SHEET="Item_Partial_List_56";
2138 GETNAMES=YES;
2139
2140 run;

NOTE: The import data set has 1 observations and 28 variables.
NOTE: TEMPSAS.ITEMACPSA_56 data set was successfully created.
NOTE: PROCEDURE IMPORT used (Total process time):
real time 0.04 seconds
cpu time 0.03 seconds

Re: How do you force a column variable to be character, not numeric?

Tom — Mon, 29 Apr 2019 21:23:26 GMT

If you want it to be character in the SAS dataset then make sure that it is character in the XLSX file.

Also I think that all of the cells in that column are empty in the XLSX file then SAS will make it numeric.

Re: How do you force a column variable to be character, not numeric?

ballardw — Mon, 29 Apr 2019 21:45:04 GMT

Save the XLSX to CSV and write a data step to read the CSV file. You can specify consistent variable names, types, lengths and informats.

Every time you use Proc Import a different set of guesses is made by the procedure and with XLSX files made after examining a very small number of rows. So if you have different spreadsheets that supposedly have the same layout you can get different variable lengths and types, if not variable names depending on what is going in with whatever makes the spreadsheet file.

Re: How do you force a column variable to be character, not numeric?

Afor910327 — Tue, 30 Apr 2019 13:46:55 GMT

I did it, and it does not even import it. I made sure it was saved as a csv file.

PROC IMPORT DATAFILE= "&drive\ACPSA_Items\Item_Partials\&prodyr\ACPSA_Items_Partials_56.csv"
OUT= tempsas.itemACPSA_56
DBMS=csv REPLACE;
SHEET="Item_Partial_List_56";
GETNAMES=YES;

run;

Re: How do you force a column variable to be character, not numeric?

Afor910327 — Tue, 30 Apr 2019 13:50:17 GMT

I did it, and SAS still read it as a numeric.

Re: How do you force a column variable to be character, not numeric?

Tom — Tue, 30 Apr 2019 13:50:35 GMT

The point of saving it as a CSV file was so you could read it with a data step that you created. That way you have control over how the variables are defined. Using PROC IMPORT on a CSV file is probably even more volatile than using it on an XLSX file. At least an XLSX file has some potential to have metadata about what types of values a column (variable) contains. A CSV file only have text for all values.

Re: How do you force a column variable to be character, not numeric?

Tom — Tue, 30 Apr 2019 14:03:26 GMT

That seems strange. That is not what it does for me with SAS 9.4m5.

I made a simple text XLSX file.

And the read it into a SAS dataset using PROC IMPORT with DBMS=XLSX.

1820  proc import datafile='c:\downloads\xlsx_type_test.xlsx' dbms=xlsx
1821    out=test1 replace
1822  ;
1823  run;

NOTE: One or more variables were converted because the data type is not supported by the V9 engine. For
      more details, run with options MSGLEVEL=I.
NOTE: The import data set has 2 observations and 6 variables.
NOTE: WORK.TEST1 data set was successfully created.
NOTE: PROCEDURE IMPORT used (Total process time):
      real time           0.01 seconds
      cpu time            0.01 seconds


1824
1825  proc contents data=test1 varnum;
1826  run;

NOTE: PROCEDURE CONTENTS used (Total process time):
      real time           0.01 seconds
      cpu time            0.01 seconds


1827  proc print data=test1; run;

NOTE: There were 2 observations read from the data set WORK.TEST1.
NOTE: PROCEDURE PRINT used (Total process time):
      real time           0.01 seconds
      cpu time            0.01 seconds

And it made the column will only empty cells as character variable of length $1.

Re: How do you force a column variable to be character, not numeric?

ballardw — Tue, 30 Apr 2019 15:06:40 GMT

@Afor910327 wrote:

I did it, and it does not even import it. I made sure it was saved as a csv file.

PROC IMPORT DATAFILE= "&drive\ACPSA_Items\Item_Partials\&prodyr\ACPSA_Items_Partials_56.csv"
OUT= tempsas.itemACPSA_56
DBMS=csv REPLACE;
SHEET="Item_Partial_List_56";
GETNAMES=YES;

run;

If it did not import anything then there was some error. What did the LOG show after you run this code? SHEET is not a valid option for CSV. You would also want to use GUESSINGROWS=MAX; to examine more rows of the data before setting the variable properties.

ALWAYS check the log.

And a sideline: The log will have a basic data step program that you can modify as needed. Copy from the Log and paste into the editor and clean up.

Re: How do you force a column variable to be character, not numeric?

Afor910327 — Tue, 30 Apr 2019 15:11:42 GMT

Hi Ballard,

Below is the log:

2037 PROC IMPORT DATAFILE= "&drive\ACPSA_Items\Item_Partials\&prodyr\ACPSA_Items_Partials_56.csv"
SYMBOLGEN: Macro variable DRIVE resolves to j:\IEA Products\ACPSA
SYMBOLGEN: Macro variable PRODYR resolves to 2019
2038 OUT= tempsas.itemACPSA_56
2039 DBMS=csv REPLACE;
NOTE: The previous statement has been deleted.
NOTE: The previous statement has been deleted.
2040 SHEET="Item_Partial_List_56";
-----
180
ERROR 180-322: Statement is not valid or it is used out of proper order.

2041 GETNAMES=YES;
2042
2043 run;

NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE IMPORT used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds

Re: How do you force a column variable to be character, not numeric?

ballardw — Tue, 30 Apr 2019 15:50:17 GMT

@Afor910327 wrote:

Hi Ballard,

Below is the log:

2037 PROC IMPORT DATAFILE= "&drive\ACPSA_Items\Item_Partials\&prodyr\ACPSA_Items_Partials_56.csv"
SYMBOLGEN: Macro variable DRIVE resolves to j:\IEA Products\ACPSA
SYMBOLGEN: Macro variable PRODYR resolves to 2019
2038 OUT= tempsas.itemACPSA_56
2039 DBMS=csv REPLACE;
NOTE: The previous statement has been deleted.
NOTE: The previous statement has been deleted.
2040 SHEET="Item_Partial_List_56";
-----
180
ERROR 180-322: Statement is not valid or it is used out of proper order.

2041 GETNAMES=YES;
2042
2043 run;

NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE IMPORT used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds

As I mentioned, SHEET is not a valid statement when importing CSV. Remove it.

Do add: GUESSINGROWS=MAX;

Re: How do you force a column variable to be character, not numeric?

Afor910327 — Tue, 30 Apr 2019 18:44:04 GMT

Nothing has really helped, now I was able to import it as a csv file, then I get everything stringed into one line as if it was one variable for the entire row.

Re: How do you force a column variable to be character, not numeric?

Tom — Tue, 30 Apr 2019 19:45:55 GMT

@Afor910327 wrote:
Nothing has really helped, now I was able to import it as a csv file, then I get everything stringed into one line as if it was one variable for the entire row.

That is usually a sign that you told SAS to use the wrong delimiter. If you have a "real" Comma Separated Values file then the delimiter is a comma. But many people generate files using other delimiters and still call them CSV files. For example in places where they use comma as the decimal place character the standard usage is to use semi-colon as the delimiter in "CSV" files.

Re: How do you force a column variable to be character, not numeric?

Reeza — Tue, 30 Apr 2019 19:51:36 GMT

Show your code and log. And ideally include a small subset or snapshot of your data.