BookmarkSubscribeRSS Feed
SASPhile
Quartz | Level 8


i'm importing data from excel and one of the columns is formatted as shown below:

col_length
8
$200
$200
3

So when I read in using proc import  the col_length in dataset is displayed as below:

Col_length
$8
$200
$200
$3


How to read the col_length as just a text so I can see as below:

Col_length
8
$200
$200
3

6 REPLIES 6
ChrisNZ
Tourmaline | Level 20

Have you tried using the guessing rows option?

Do you have non-numeric values in these columns?

 

In any case, you can transform the columns back to numeric:

 

VAR1N = input(VAR1, ?? 32.10);

 

 

 

SASPhile
Quartz | Level 8

I dont want to convert to numeric. I want them to be displayed as they are. But what happens is in excel the value are 8,$200, but after importing the sas dataset displays $8,$200.

 

ChrisNZ
Tourmaline | Level 20

Maybe 

format VAR comma8.0;

then?

If not, run proc contents on the table please.

SASPhile
Quartz | Level 8
Guessing rows doesnt work with DBMS=XLSX

PROC IMPORT OUT= WORK.input_metadata DATAFILE=
"C:\temp\reference.xlsx"
DBMS=xlsx REPLACE;
SHEET="input_Variables";
GETNAMES=YES;GUESSINGROWS=40;
RUN;
NOTE: The previous statement has been deleted.
12096 SHEET="input_Variables";
12097 GETNAMES=YES;GUESSINGROWS=40;
------------
180
ERROR 180-322: Statement is not valid or it is used out of proper order.

Tom
Super User Tom
Super User

Sounds like you have a numeric column in Excel where some cells are using one display format and other cells are using a different display format.  That can work in a spreadsheet since you can treat each cell as a totally independent object.  But in a dataset one variable can have only one type and one display format.

 

So if you really want a variable that can have a value that displays as 8 for one observation and another value that displays as $200 on another then you need to make sure that the variable is a CHARACTER variable.  

 

You can force SAS to import your column from Excel as a character variable by making at least one of the cells in the column be an actual actual character value. SAS will convert columns that have mixed numeric and character values into character variables in the SAS dataset.  But I am not sure that SAS will then add the dollar signs to numeric cells that have display format that adds dollar signs.  I think SAS will just convert the actual number that is stored in the cell into a string of digits.  So you might need to transform your full column to contain character values. 

 

Another way is to instead create another column in your Excel file that you can use to tell you which format you want to use to transform the value in the nuneric column.  Then you could write some SAS code to use the PUTN() function to convert your numbers into the character strings that you want to see.

 

 

SASPhile
Quartz | Level 8

 
In excel:


 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 996 views
  • 0 likes
  • 3 in conversation