topic Re: Getting error while importing delimiter files in SAS Studio

Getting error while importing delimiter files

u39734216 — Wed, 14 Aug 2019 06:38:22 GMT

Hi All,

I was trying to import delimiter file which has space is a delimiter like this - Adelanto 15515 361 363 2 2 5 117 120 68 47 2

but I was getting missing values in some columns. I was trying this code-

data crime_stats;
infile 'Folder\MyFolder\SASUniversityEdition\Crime_Stats.dat' DSD dlm=' ';
input City_name & $13. Population Crime_index Total_crime_index Murder Forcible_rape Robbery Aggravated_assault Burglary Larceny_theft Motor_vehicle_theft Arson;
run;

I have used DSD and truncover in infile statement but some columns giving me missing values. pls, can anyone tell me how can I do this?

Re: Getting error while importing delimiter files

PeterClemmensen — Wed, 14 Aug 2019 07:03:06 GMT

Do you have only 1 space between each value in your dat file? The DSD option interprets two delimiters side by side as a missing value

Re: Getting error while importing delimiter files

u39734216 — Wed, 14 Aug 2019 07:18:28 GMT

it has two spaces.

Re: Getting error while importing delimiter files

PeterClemmensen — Wed, 14 Aug 2019 07:21:45 GMT

I can't see your data, so I can not test for you. But try to drop the DSD option then.

Re: Getting error while importing delimiter files

Kurt_Bremser — Wed, 14 Aug 2019 07:28:18 GMT

Please post example data into a subwindow opened with the {i} button (this will keep all text as is). Make sure to include lines that give you problems.

Re: Getting error while importing delimiter files

u39734216 — Wed, 14 Aug 2019 08:39:37 GMT

This is the data I'm working

Adelanto  15515  361  363  2  2  5  117  120  68  47  2
Agoura Hills  21127  290  290  0  3  5  48  64  155  15  0
Alameda  79848  2933  2950  0  12  91  146  323  2022  339  17

KB I inserted three text lines from the file with {i}

Re: Getting error while importing delimiter files

Kurt_Bremser — Wed, 14 Aug 2019 08:48:33 GMT

Then this should do it:

data crime_stats;
infile 'Folder\MyFolder\SASUniversityEdition\Crime_Stats.dat';
length City_name $30;
input City_name & Population Crime_index Total_crime_index Murder Forcible_rape Robbery Aggravated_assault Burglary Larceny_theft Motor_vehicle_theft Arson;
run;

Note that I made City_name a lot longer, as you have several city names that are much longer than 13 characters.

Re: Getting error while importing delimiter files

u39734216 — Wed, 14 Aug 2019 13:04:35 GMT

I'm getting missing values in these columns - crime_index murder robbery burglary motor_vehicle_theft.

Re: Getting error while importing delimiter files

ballardw — Wed, 14 Aug 2019 14:34:45 GMT

@u39734216 wrote:

I'm getting missing values in these columns - crime_index murder robbery burglary motor_vehicle_theft.

Copy the code and any messages from the Log. Paste the copied log into a code box opened using the forum's {I} or "running man" icon.

I ran @Kurt_Bremser's code on the example data provided and had no missing values.

Re: Getting error while importing delimiter files

Reeza — Wed, 14 Aug 2019 15:07:20 GMT

That filepath cannot be correct. So either this isn't the code you're using or you have errors above this that you should resolve first.

Re: Getting error while importing delimiter files

u39734216 — Sun, 18 Aug 2019 06:28:49 GMT

I'm getting missing values in columns.

Re: Getting error while importing delimiter files

art297 — Sun, 18 Aug 2019 14:31:51 GMT

I ran @Kurt_Bremser 's code, as follows, only changing the file name and there were no missing values for the variables you mentioned:

data crime_stats;
  infile '/folders/myfolders/Crime_Stats.dat.txt';
  length City_name $30;
  input City_name & Population Crime_index Total_crime_index Murder Forcible_rape Robbery Aggravated_assault Burglary Larceny_theft Motor_vehicle_theft Arson;
run;

Art, CEO, AnalystFinder.com

Re: Getting error while importing delimiter files

Tom — Sun, 18 Aug 2019 15:28:51 GMT

@u39734216 wrote:

This is the data I'm working
Adelanto  15515  361  363  2  2  5  117  120  68  47  2
Agoura Hills  21127  290  290  0  3  5  48  64  155  15  0
Alameda  79848  2933  2950  0  12  91  146  323  2022  339  17
KB I inserted three text lines from the file with {i}

That seems easy enough to read.

data test;
  infile "&path/crime_stats.dat.txt" truncover;
  input city &:$30. stat1-stat11;
run;

Obs  city            stat1  stat2  stat3  stat4  stat5  stat6  stat7  stat8  stat9  stat10  stat11

  1  Adelanto        15515    361    363     2      2      5     117    120     68     47       2
  2  Agoura Hills    21127    290    290     0      3      5      48     64    155     15       0
  3  Alameda         79848   2933   2950     0     12     91     146    323   2022    339      17
  4  Albany          17457    736    742     0      1     37      12    169    462     55       6
  5  Alhambra        85356   2042   2057     4      3    165      97    372   1114    287      15
  6  Anaheim        299477  10104  10157    16     85    468    1070   1640   5452   1373      53
  7  Antioch         82621   2500   2535     3     14    120     382    510   1085    386      35
  8  Apple Valley    57267   2006   2020     2      8     60     146    531   1072    187      14

Re: Getting error while importing delimiter files

Kurt_Bremser — Tue, 20 Aug 2019 05:48:51 GMT

@u39734216 wrote:

I'm getting missing values in columns.

Then you did not run my code as I posted it, or your data is different from what you gave us.