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?
@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 17KB 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
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
it has two spaces.
I can't see your data, so I can not test for you. But try to drop the DSD option then.
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}
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.
I'm getting missing values in these columns - crime_index murder robbery burglary motor_vehicle_theft.
@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.
I'm getting missing values in columns.
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
@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.
@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 17KB 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
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.