topic Re: Have trouble reading in fixed-space data in SAS Programming

Have trouble reading in fixed-space data

jc3992 — Tue, 23 Jul 2019 02:57:53 GMT

Hello everyone,

[Updated]

Thank you very much for all your helps.

after saving to CSV file. It worked.

Thank you very much

proc import datafile="&dirdata.umaru.csv" out=umaru dbms=csv
            replace;
     delimiter=',';
     getnames=yes;
run;

proc print data=umaru;
run;

I have been a while not use SAS but I am not so sure of this.

The data is like this:

The biggest column for "id" is 3 integers

for variable "back" the biggest space is 6.

What I used was:

Data umaru;
  INFILE "&dirdata.umaru.xlsx" dlm="," dsd;
  INPUT id 1-3 age  beck 7. ivhx 14-15  ndrugtx 16-17  race 18-19 treat 20-21 site 22-23  dfree 24-25;
  run;

Thank you very much!

A appreciated all the advice. Thanks!

id	age	beck	ivhx	ndrugtx	race	treat	site	dfree
1	39	9	3	1	0	1	0	0
2	33	34	2	8	0	1	0	0
3	33	10	3	3	0	1	0	0

Re: Have trouble reading in fixed-space data

andreas_lds — Mon, 22 Jul 2019 05:44:48 GMT

Excel files can't be read with indole & input. You need either proc import or a libname statement.
If your file just ends with xlsx but is a normal text-file (you can open it with notepad) then reading it with infile & input is possible. You should just look up the input statement in the docs. Hint: specify start and end for each variable as you did for id.

Re: Have trouble reading in fixed-space data

ballardw — Mon, 22 Jul 2019 15:03:34 GMT

Or save the XLSX as CSV and use a delimited input.

It is hard to get fixed column text out of Excel but easy to get delimited: go to file>Save As> select CSV

Re: Have trouble reading in fixed-space data

Reeza — Mon, 22 Jul 2019 15:54:39 GMT

XLSX files are not fixed space data, it's data stored in a proprietary format. Have you tried using PROC IMPORT?