topic Re: Importing data in SAS Programming

Importing data

lmyers2 — Wed, 02 May 2018 02:23:51 GMT

Hello - this should be simple but for some reason my data isn't importing correctly! For some reason my last variable Count4 comes in missing as periods. When I look at the original data file, it looks fine (not a symbol or something at the Count4 position). Can someone help? Code below.

Thanks

Laura

data new; infile 'LOCATION';
input ID Group age Count1 Count2 Count3 Count4;
datalines;
run;

Re: Importing data

Reeza — Wed, 02 May 2018 02:30:23 GMT

You're going to have to provide more information, in this case, most likely a sample of the data so we can test the code. If you can't provide actual data, you'll need to mock up some fake data.

You should ideally include the log, especially if there are any warnings, errors or notes beyond the normal record/time count.

@lmyers2 wrote:

Hello - this should be simple but for some reason my data isn't importing correctly! For some reason my last variable Count4 comes in missing as periods. When I look at the original data file, it looks fine (not a symbol or something at the Count4 position). Can someone help? Code below.

Thanks

Laura
data new; infile 'LOCATION';
input ID Group age Count1 Count2 Count3 Count4;
datalines;
run;

Re: Importing data

lmyers2 — Wed, 02 May 2018 10:51:44 GMT

Here it is! It's so strange how it's not importing the last column...would love your help!

ID Group Age Count1 Count2 Count3 Count4

1 0 18 11 5 3 3
2 0 35 11 3 5 3
3 1 26 6 2 4 0

Re: Importing data

emrancaan — Wed, 02 May 2018 10:59:22 GMT

open your datafile in notepad++ and use show all characters option to see what are the additional characters causing this

I cleaned your sample data and it worked fine

data new; 
/*infile 'LOCATION';*/
input ID Group age Count1 Count2 Count3 Count4;
datalines;
1 0 18 11 5 3 3 
2 0 35 11 3 5 3 
3 1 26 6 2 4 0 
;
run;

Re: Importing data

lmyers2 — Wed, 02 May 2018 11:03:52 GMT

Here is the error message from SAS

NOTE: Invalid data for Count4 in line 1 59-60.
RULE: ----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8----+---

1 CHAR 1 0 31 11 5 3 3 3. 60
ZONE 222222222223222223222222332222223322222232222232222232222230
NUMR 00000000000100000000000031000000110000005000003000003000003D

Re: Importing data

emrancaan — Wed, 02 May 2018 11:23:05 GMT

Occasionally programmers get invalid-data messages because they are trying to read unprintable or other non-standard characters such as carriage returns. At those times SAS prints two more lines of data labeled ZONE and NUMR. These lines are the hexadecimal representation of the raw data. You don’t have to be able to read hexadecimal to be able to interpret this. SAS prints the data this way

because the normal 10 numerals and 26 letters don’t provide enough values to represent all computer symbols uniquely. Hexadecimal uses two characters to represent each symbol. To read hexadecimal, take a digit from the ZONE line together with the corresponding digit from the NUMR line.

Re: Importing data

lmyers2 — Wed, 02 May 2018 14:37:28 GMT

Thanks - is there a notepad++ version for mac? Is it that there is or isn't a hard return or tab after each row of the data in dataline?

Best

Laura

Re: Importing data

Reeza — Wed, 02 May 2018 14:41:28 GMT

For a Mac, I use TextWrangler, the free version.

Re: Importing data

ballardw — Wed, 02 May 2018 15:24:46 GMT

@lmyers2 wrote:

Here is the error message from SAS

NOTE: Invalid data for Count4 in line 1 59-60.
RULE: ----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8----+---

1 CHAR 1 0 31 11 5 3 3 3. 60
ZONE 222222222223222223222222332222223322222232222232222232222230
NUMR 00000000000100000000000031000000110000005000003000003000003D

Pasting anything from the log into a code box opened with the forum's {I} menu icon improves readability to considerable margin as the code box is plain text and doesn't get reformatted or use proportional fonts that make lining up columns as above

For instance all those columns with 2 over 0 represent spaces. But the line that appears above ZONE has been reformatted by the message window to remove multiple spaces

This invalid data note should look more like:

NOTE: Invalid data for Count4 in line 1 59-60.
RULE:  ----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8----+---

1 CHAR            1     0      31      11      5     3     3     3. 60
ZONE   222222222223222223222222332222223322222232222232222232222230
NUMR   00000000000100000000000031000000110000005000003000003000003D

Re: Importing data

emrancaan — Wed, 02 May 2018 15:29:55 GMT

post at least one line of data so we can check what is wrong though problem seems here 3.

Re: Importing data

ballardw — Wed, 02 May 2018 16:20:41 GMT

@lmyers2 wrote:

Hello - this should be simple but for some reason my data isn't importing correctly! For some reason my last variable Count4 comes in missing as periods. When I look at the original data file, it looks fine (not a symbol or something at the Count4 position). Can someone help? Code below.

Thanks

Laura
data new; infile 'LOCATION';
input ID Group age Count1 Count2 Count3 Count4;
datalines;
run;

Maybe time to describe your environment. Are you running SAS stand alone on your machine or connecting to a server? Are you running SAS for Mac or for Windows in an emulator window? If connecting to a server what OS is the server?

A common issue is to have source files from one operating system which use different end of line indicators and reading them in a different environment.

You might try adding this option to your INFILE statement: TERMSTR=CR and see if that helps. If so then it is one of these file format issues.