topic Re: Import data through data step in SAS Programming

Import data through data step

Job04 — Fri, 26 Nov 2021 09:10:34 GMT

I'm trying to import csv file using data step. Data consits of two columns : country and rate. The problem is the lengths of countries are different and also there an embedded spaces in the names, and the values of the second column are all over. This is how the data looks in text format:

I used this code:

data sales;

infile "………………………." trunvcover ;

input names $ 1-14 rate 16-20;

Run;

But data was not imported properly and I got no error message in my log:

The minimum record length was 5.
The maximum record length was 14.
NOTE: The data set WORK.SALES has 9751 observations and 2 variables.

and the output looks like this:

Could anyone please suggest solution to this.

Thank you

Re: Import data through data step

andreas_lds — Fri, 26 Nov 2021 11:33:45 GMT

Please post an excerpt of the data as text, not as screenshot, so that we can use the data.

Untested:

data sales;
  length Name $ 20 Rate 8;
  infile .... ;
  input;
  if lengthn(strip(_infile_)) > 0;
  Name = substr(_infile_, 1, anydigit(_infile_)-1);
  Rate = input(substr(_infile_, anydigit(_infile_)),  best.);
run;

Re: Import data through data step

Kurt_Bremser — Fri, 26 Nov 2021 12:02:58 GMT

Open your original file with a text editor (not with Excel or similar), copy a part of it and paste it into a window opened with this button:

Then we will be able to help you.

Re: Import data through data step

Reeza — Fri, 26 Nov 2021 16:57:48 GMT

I'd read it in as a single string and then parse it in SAS.
If you look at it backwards, the number is always at the end.
Then everything else becomes your country name.
Or COMPRESS() to remove all digits and period at the end and Compress to keep all alpha is another option. There's a few ways to parse it once it's read in.

Re: Import data through data step

Job04 — Fri, 26 Nov 2021 17:09:22 GMT

I tried that code and it didn't work.

Austria 231.78 4.313 82.41 9043072
Austria 153.819 1.548 82.41 9043072
Austria 162.113 5.419 82.41 9043072
Austria 181.575 3.649 82.41 9043072
Austria 255.555 11.058 82.41 9043072
Austria 273.027 7.52 82.41 9043072
Austria 280.878 4.755 82.41 9043072
Austria 228.13 8.072 82.41 9043072
Austria 251.906 5.087 82.41 9043072
Austria 182.571 3.981 82.41 9043072
Austria 169.854 2.654 82.41 9043072
Austria 174.166 7.962 82.41 9043072
Austria 211.985 5.419 82.41 9043072
Austria 388.142 5.861 82.41 9043072
Austria 168.969 7.298 82.41 9043072
Austria 190.533 7.298 12 82.41 9043072
Austria 140.107 3.207 394 82.41 9043072
Austria 128.386 4.423 1307 82.41 9043072
Austria 164.325 8.515 2807 82.41 9043072
Austria 184.782 4.202 4132 82.41 9043072
Austria 188.21 5.64 4306 82.41 9043072
Austria 230.895 4.644 4914 82.41 9043072
Austria 179.806 6.524 4919 82.41 9043072
Austria 132.919 3.207 4931 82.41 9043072
Austria 111.577 3.649 5049 82.41 9043072
Austria 156.695 7.077 5974 82.41 9043072
Austria 181.465 5.419 6512 82.41 9043072
Austria 160.233 4.755 9202 82.41 9043072
Austria 165.873 5.64 19008 82.41 9043072

Austria 719.446 2.212 5650776 57.41 9043072
Austria 950.341 2.101 5660006 57.41 9043072
Austria 1038.143 3.539 5671516 57.41 9043072
Austria 1099.516 3.428 5677339 57.41 9043072
Austria 945.917 2.212 5679691 57.41 9043072
Austria 904.339 1.438 5686359 49.07 9043072
Austria 852.808 4.313 5695643 49.07 9043072
Austria 1260.412 2.543 5705493 49.07 9043072
Austria 1324.218 2.654 5717671 49.07 9043072
Austria 1304.645 4.423 5732401 49.07 9043072
Austria 1454.373 5.308 5741822 49.07 9043072
Austria 1277.442 1.88 5746307 49.07 9043072
Austria 1314.708 4.423 5755649 59.26 9043072
Austria 1145.96 6.745 5767024 59.26 9043072
Austria 1594.149 4.534 5779929 59.26 9043072
Austria 1674.763 6.082 5793907 59.26 9043072
Austria 1748.189 5.308 5812738 59.26 9043072

Belgium 222.053 4.384 1480 60.19 11632334
Belgium 155.773 4.556 86.006 1504 60.19 11632334
Belgium 102.473 3.009 1550 60.19 11632334
Belgium 153.366 5.588 3349 60.19 11632334
Belgium 259.621 4.642 6842 62.96 11632334
Belgium 238.559 4.212 14807 62.96 11632334
Belgium 230.65 3.095 24040 62.96 11632334
Belgium 233.573 4.126 26974 62.96 11632334
Belgium 199.1 2.235 72.641 27325 62.96 11632334
Belgium 108.663 2.751 31315 62.96 11632334
Belgium 159.383 4.212 45807 62.96 11632334
Belgium 268.562 3.697 66663 62.96 11632334
Belgium 210.792 3.783 91177 62.96 11632334
Belgium 222.225 3.009 114301 62.96 11632334
Belgium 209.588 4.9 119661 62.96 11632334
Belgium 149.583 3.181 80.538 122766 62.96 11632334
Belgium 75.049 2.923 130702 62.96 11632334
Belgium 159.125 4.212 150636 62.96 11632334
Belgium 224.976 3.439 169931 62.96 11632334

Belgium 1584.979 3.439 8647984 45.83 11632334
Belgium 1435.567 3.611 8652215 45.83 11632334
Belgium 1848.468 3.611 8655713 11632334
Belgium 0 0 8658714 11632334
Belgium 0 0 8658729 11632334
Bulgaria 39.729 4.06 53.7 6896655
Bulgaria 49.299 5.8 53.7 6896655
Bulgaria 24.795 4.93 53.7 6896655
Bulgaria 149.203 22.765 53.7 6896655
Bulgaria 189.947 9.715 53.7 6896655
Bulgaria 145.288 8.555 53.7 6896655
Bulgaria 125.713 8.12 53.7 6896655
Bulgaria 109.183 8.845 53.7 6896655
Bulgaria 57.129 2.755 53.7 6896655
Bulgaria 15.225 4.205 53.7 6896655
Bulgaria 89.899 15.37 53.7 6896655
Bulgaria 108.748 6.815 53.7 6896655
Bulgaria 77.574 10.15 53.7 6896655
Bulgaria 77.574 6.815 53.7 6896655
Bulgaria 80.039 8.845 53.7 6896655
Bulgaria 33.784 2.465 53.7 6896655
Bulgaria 11.165 1.305 53.7 6896655
Bulgaria 82.649 11.89 1114 53.7 6896655
Bulgaria 78.879 7.105 3168 53.7 6896655
Bulgaria 69.889 5.365 4293 53.7 6896655
Bulgaria 65.974 13.05 4532 53.7 6896655

Croatia 166.599 5.39 1777373 37.96 4081657
Croatia 823.195 7.35 1781470 37.96 4081657
Croatia 1119.888 6.37 37.96 4081657
Croatia 1017.724 6.37 1791056 37.96 4081657
Croatia 1076.034 6.37 1795287 37.96 4081657
Croatia 1114.008 7.35 37.96 4081657
Croatia 813.15 5.39 275.987 1798069 37.96 4081657
Croatia 275.868 7.595 1798141 37.96 4081657
Croatia 192.324 9.8 1802284 37.96 4081657
Croatia 1120.378 10.045 1806387 37.96 4081657
Croatia 1545.941 7.84 1811801 37.96 4081657
Croatia 1698.33 8.82 1816377 37.96 4081657
Croatia 1738.02 12.25 1820138 37.96 4081657
Croatia 1018.949 12.25 397.224 37.96 4081657
Croatia 304.288 11.27 37.96 4081657
Croatia 1216.663 14.455 1831401 37.96 4081657
Croatia 1792.164 12.25 1837846 35.19 4081657
Croatia 1711.805 16.17 35.19 4081657

Cyprus 148.437 2.232 10024 80.56 896005
Cyprus 147.321 1.116 78.828 80.56 896005
Cyprus 128.347 1.116 80.56 896005
Cyprus 117.187 2.232 80.56 896005
Cyprus 106.026 0 80.56 896005
Cyprus 126.115 3.348 80.56 896005
Cyprus 114.955 1.116 80.56 896005
Cyprus 126.115 1.116 14837 80.56 896005
Cyprus 113.839 1.116 60.81 80.56 896005
Cyprus 137.276 1.116 80.56 896005
Cyprus 126.115 1.116 80.56 896005
Cyprus 90.401 3.348 80.56 896005
Cyprus 135.044 2.232 80.56 896005
Cyprus 119.419 1.116 80.56 896005
Cyprus 123.883 1.116 19221 80.56 896005
Cyprus 119.419 0 74.324 80.56 896005
Cyprus 119.419 0 80.56 896005
Cyprus 146.205 1.116 20323 80.56 896005

Estonia 95.081 0 39.128 582191 23.15 1325188
Estonia 110.928 0 585027 23.15 1325188
Estonia 218.082 0 588215 23.15 1325188
Estonia 259.586 1.509 591022 23.15 1325188
Estonia 280.715 0 593767 23.15 1325188
Estonia 205.254 0 596814 23.15 1325188
Estonia 218.837 0 599331 23.15 1325188
Estonia 152.431 0 54.177 600479 23.15 1325188
Estonia 123.001 0 603458 23.15 1325188
Estonia 270.905 0 606080 23.15 1325188
Estonia 215.818 1.509 608764 23.15 1325188
Estonia 269.396 0 612119 23.15 1325188
Estonia 249.776 0 612873 23.15 1325188
Estonia 164.505 0 614153 23.15 1325188
Estonia 76.216 0 54.93 614585 23.15 1325188
Estonia 240.721 3.018 616794 23.15 1325188
Estonia 282.224 0 619135 23.15 1325188
Estonia 329.765 0 621748 23.15 1325188
Estonia 304.862 0.755 623866 28.7 1325188
Estonia 264.868 0.755 626465 28.7 1325188
Estonia 242.984 0 628411 28.7 1325188
Estonia 249.021 1.509 85.028 630605 28.7 1325188

France 549.521 1.939 180.481 2670306 68.52 67564251
France 134.775 5.328 2724543 68.52 67564251
France 454.723 5.639 2794128 68.52 67564251
France 873.983 4.485 2866986 68.52 67564251
France 750.042 4.559 2954702 68.52 67564251
France 690.942 4.914 3046736 68.52 67564251
France 2.457 2.753 3108520 68.52 67564251
France 1193.457 2.738 194.147 3127164 68.52 67564251
France 159.744 2.916 3138148 71.3 67564251
France 119.235 6.305 3245894 75 67564251
France 13.72 6.231 3361248 75 67564251
France 1436.159 5.077 3482576 75 67564251
France 2.427 4.899 3598966 75 67564251
France 20.78 3.064 3681655 75 67564251
France 1745.035 2.605 195.588 3716216 75 67564251
France 126.546 5.698 3831334 75 67564251
France 579.152 5.106 3976163 75 67564251

Greece 370.465 7.714 268.04 6464917 10370747
Greece 670.154 6.943 6472459 10370747
Greece 806.499 8.196 6481011 10370747
Greece 641.516 8.582 6489186 10370747
Greece 701.589 6.075 6493402 10370747
Greece 747.391 8.968 6502689 10370747
Greece 573.151 6.653 6512821 10370747
Greece 396.114 7.328 6513709 10370747
Hungary 286.896 13.494 72.22 9634162
Hungary 146.354 11.833 72.22 9634162
Hungary 135.663 10.691 72.22 9634162
Hungary 88.85 9.653 72.22 9634162
Hungary 90.304 10.691 72.22 9634162
Hungary 212.473 12.248 72.22 9634162
Hungary 318.45 13.182 72.22 9634162
Hungary 301.739 11.937 72.22 9634162
Hungary 281.913 11.833 72.22 9634162
Hungary 184.552 9.757 72.22 9634162
Hungary 147.288 7.992 72.22 9634162

Ireland 636.978 11.238 3756681 48.15 4982904
Ireland 606.875 0 3757883 48.15 4982904
Ireland 783.278 0 3758869 48.15 4982904
Ireland 739.529 0 3760182 48.15 4982904
Ireland 687.952 0 74.933 3760944 48.15 4982904
Ireland 634.369 0 3761410 48.15 4982904
Ireland 1069.858 0 3762380 48.15 4982904
Ireland 595.637 14.851 3763636 48.15 4982904
Ireland 737.923 0 3764659 48.15 4982904
Ireland 1099.359 0 3765681 48.15 4982904
Ireland 931.585 0 3766990 48.15 4982904
Ireland 763.611 0 102.328 3768771 48.15 4982904
Ireland 0 0 3769282 48.15 4982904
Ireland 1799.152 0 3770261 48.15 4982904
Ireland 728.892 8.63 3771253 48.15 4982904
Ireland 932.388 0 3773593 48.15 4982904
Ireland 628.75 0 3774917 48.15 4982904
Ireland 1195.889 0 3777756 48.15 4982904
Ireland 839.069 0 3780486 4982904
Italy 367.913 7.653 82.41 60367471
Italy 195.884 6.03 82.41 60367471
Italy 235.971 5.748 154.188 82.41 60367471
Italy 178.871 5.765 9 82.41 60367471

Luxembourg 0 0 17.569 41.67 634814
Luxembourg 389.09 0 41.67 634814
Luxembourg 211.085 0 41.67 634814
Luxembourg 185.881 0 41.67 634814
Luxembourg 193.758 0 41.67 634814
Luxembourg 200.059 1.575 41.67 634814
Luxembourg 0 0 41.67 634814
Luxembourg 0 0 28.749 41.67 634814
Luxembourg 356.01 1.575 41.67 634814
Luxembourg 140.199 0 37.96 634814
Luxembourg 222.112 0 37.96 634814
Luxembourg 148.075 0 37.96 634814
Luxembourg 162.252 0 37.96 634814
Luxembourg 0 0 37.96 634814
Luxembourg 0 0 23.958 37.96 634814
Luxembourg 218.962 1.575 37.96 634814
Luxembourg 135.473 0 37.96 634814
Luxembourg 168.553 0 37.96 634814
Luxembourg 113.419 0 37.96 634814
Luxembourg 85.064 0 37.96 634814
Luxembourg 0 0 37.96 634814
Luxembourg 0 0 15.972 37.96 634814
Luxembourg 189.032 0 37.96 634814
Luxembourg 81.914 0 37.96 634814
Luxembourg 160.677 3.151 37.96 634814
Luxembourg 107.118 0 37.96 634814
Luxembourg 78.763 0 37.96 634814
Luxembourg 0 0 37.96 634814
Luxembourg 0 0 11.18 37.96 634814
Luxembourg 209.51 1.575 37.96 634814
Luxembourg 66.161 0 37.96 634814
Luxembourg 96.091 3.151 37.96 634814
Luxembourg 74.037 0 37.96 634814
Luxembourg 78.763 1.575 37.96 634814
Luxembourg 0 0 37.96 634814
Luxembourg 0 0 19.166 37.96 634814
Luxembourg 149.65 0 37.96 634814
Luxembourg 140.199 0 37.96 634814
Luxembourg 119.72 1.575 37.96 634814
Luxembourg 99.242 1.575 37.96 634814

Malta 499.903 3.875 25118 52.78 516100
Malta 459.213 0 26781 52.78 516100
Malta 509.591 3.875 42.755 28048 52.78 516100
Malta 339.082 1.938 29021 52.78 516100
Malta 651.037 5.813 29748 52.78 516100
Malta 451.463 3.875 30329 52.78 516100
Malta 701.414 7.75 31034 52.78 516100
Malta 548.343 5.813 31710 52.78 516100
Malta 668.475 1.938 32420 52.78 516100
Malta 352.645 9.688 132.151 32862 52.78 516100

Netherlands 315.668 2.912 75 17173094
Netherlands 356.546 1.514 75 17173094
Netherlands 381.003 1.339 75 17173094
Netherlands 352.412 1.398 75.886 454352 75 17173094
Netherlands 321.782 1.048 75 17173094
Netherlands 294.996 1.922 75 17173094
Netherlands 357.536 2.737 75 17173094
Netherlands 369.066 1.98 75 17173094
Netherlands 441.213 2.737 75 17173094
Netherlands 455.655 0.932 75 17173094
Netherlands 417.106 0.932 86.801 582058 75 17173094

Netherlands 191.288 0.524 41.67 17173094
Netherlands 151.341 0.524 41.67 17173094
Netherlands 142.49 0.175 25.449 10606168 41.67 17173094
Netherlands 123.216 0.116 41.67 17173094

Slovakia 12.269 0 2053645 43.98 5460726
Slovakia 9.889 0 0.366 2058694 43.98 5460726
Slovakia 6.043 0 2067552 43.98 5460726
Slovakia 21.792 0.366 2075531 43.98 5460726
Slovakia 17.763 0.183 2085437 43.98 5460726
Slovakia 17.397 0 2094292 43.98 5460726
Slovakia 15.566 0 2102941 43.98 5460726
Slovakia 23.44 0 2106982 43.98 5460726
Slovakia 17.397 0 1.832 2108725 43.98 5460726
Slovakia 5.128 0 2114144 34.72 5460726
Slovakia 21.792 0.366 2118979 34.72 5460726
Slovakia 25.088 0.183 2124990 34.72 5460726

Spain 0 0 71.3 46745211
Spain 0 0 160.798 1070091 71.3 46745211
Spain 647.147 15.018 1096922 71.3 46745211
Spain 215.145 11.338 1119180 71.3 46745211
Spain 231.66 7.209 1144556 71.3 46745211
Spain 310.513 8.3 1171026 71.3 46745211
Spain 244.624 8.493 68.52 46745211
Spain 0 0 68.52 46745211
Spain 0 0 107.072 1197061 68.52 46745211
Spain 446.014 11.445 1208305 68.52 46745211
Spain 159.61 9.477 1221183 68.52 46745211
Spain 197.068 8.322 1231782 68.52 46745211
Spain 204.684 7.38 1243783 68.52 46745211
Spain 178.435 7.038 68.52 46745211
Spain 0 0 68.52 46745211
Spain 0 0 83.41 1261848 68.52 46745211
Spain 341.81 9.99 1266700 68.52 46745211
Spain -1590.473 4.107 1287002 68.52 46745211
Spain 131.286 9.541 1308913 68.52 46745211

Switzerland 0 3.213 48.534 46369 60.19 8715494
Switzerland 376.341 -9.982 54890 60.19 8715494
Switzerland 156.388 15.031 65949 60.19 8715494
Switzerland 183.811 2.868 76123 60.19 8715494
Switzerland 155.585 1.606 87350 60.19 8715494
Switzerland 143.767 1.836 97493 60.19 8715494
Switzerland 0 2.295 101039 60.19 8715494
Switzerland 0 2.065 37.749 101543 60.19 8715494
Switzerland 284.551 2.18 110510 60.19 8715494

Switzerland 0 0 33.848 5660446 8715494
United Kingdom 783.76 9.017 79.63 68207114
United Kingdom 848.196 6.524 79.63 68207114
United Kingdom 808.669 6.671 338.088 79.63 68207114
United Kingdom 863.883 6.011 79.63 68207114
United Kingdom 895.61 12.917 87.96 68207114
United Kingdom 917.148 15.277 87.96 68207114
United Kingdom 773.922 17.036 87.96 68207114
United Kingdom 999.778 19.543 87.96 68207114
United Kingdom 881.11 15.174 87.96 68207114
United Kingdom 806.749 8.313 422.302 391399 87.96 68207114
United Kingdom 678.448 7.756 412167 87.96 68207114
United Kingdom 669.256 18.268 428232 87.96 68207114
United Kingdom 698.783 22.989 437977 87.96 68207114
United Kingdom 715.527 18.371 443234 87.96 68207114
United Kingdom 819.343 18.84 447442 87.96 68207114
United Kingdom 607.385 19.045 450062 87.96 68207114
United Kingdom 566.95 9.999 419.223 452301 87.96 68207114

Re: Import data through data step

Job04 — Fri, 26 Nov 2021 17:11:36 GMT

Netherlands 191.288 0.524 41.67 17173094
Netherlands 151.341 0.524 41.67 17173094
Netherlands 142.49 0.175 25.449 10606168 41.67 17173094
Netherlands 123.216 0.116 41.67 17173094

Re: Import data through data step

Kurt_Bremser — Fri, 26 Nov 2021 19:53:36 GMT

This is not a csv file. A csv file should be comma-separated (or at least semicolon-separated if saved from Excel).

How did you post this data here (describe every single step)?

Re: Import data through data step

Reeza — Fri, 26 Nov 2021 19:59:39 GMT

The code provided was for the type of file you initially showed. Your new sample doesn't align with that code, so it will not work correctly.

Re: Import data through data step

Job04 — Fri, 26 Nov 2021 20:21:44 GMT

Data is csv. Saved it as text format then copied and pasted it here.

Data is from here: https://github.com/owid/covid-19-data/tree/master/public/data

I'm intrested only in these variables:

location

date new_cases_per_million

new_deaths_per_million

weekly_hosp_admissions_per_million

people_fully_vaccinated

stringency_index

population

Re: Import data through data step

Job04 — Fri, 26 Nov 2021 20:23:12 GMT

Data is from here: https://github.com/owid/covid-19-data/tree/master/public/data

I'm intrested only in these variables:

location

date new_cases_per_million

new_deaths_per_million

weekly_hosp_admissions_per_million

people_fully_vaccinated

stringency_index

population

Re: Import data through data step

Kurt_Bremser — Fri, 26 Nov 2021 20:58:56 GMT

@Job04 wrote:

Data is from here: https://github.com/owid/covid-19-data/tree/master/public/data

I'm intrested only in these variables:

location

date new_cases_per_million

new_deaths_per_million

weekly_hosp_admissions_per_million

people_fully_vaccinated

stringency_index

population

You need to pay attention to details. I downloaded a csv file from there, opened it with a text editor, and copy/pasted the first line into a </> box:

iso_code,continent,location,date,total_cases,new_cases,new_cases_smoothed,total_deaths,new_deaths,new_deaths_smoothed,total_cases_per_million,new_cases_per_million,new_cases_smoothed_per_million,total_deaths_per_million,new_deaths_per_million,new_deaths_smoothed_per_million,reproduction_rate,icu_patients,icu_patients_per_million,hosp_patients,hosp_patients_per_million,weekly_icu_admissions,weekly_icu_admissions_p

As you can see, it is a true csv (comma separated volume) file. This is what would have happened if you followed my instructions.

You can read such files with

infile "filename" dlm=',' dsd truncover;

and an INPUT statement with the correct informats and the : modifier.

Re: Import data through data step

Tom — Fri, 26 Nov 2021 22:17:44 GMT

They have published both CSV and XLSX versions of the file. If you are going to use PROC IMPORT it might be better to take the XLSX version of the file and convert from that since then PROC IMPORT will have more information (the cell types) to use when guessing how to define the variables.

Watch out as some of those fields have column headers that are longer than 32 bytes, so you cannot use them as they are as variable names in SAS.

 varnum                  name                                    label

   23      weekly_icu_admissions_per_millio    weekly_icu_admissions_per_million
   25      weekly_hosp_admissions_per_milli    weekly_hosp_admissions_per_million
   43      people_fully_vaccinated_per_hund    people_fully_vaccinated_per_hundred
   45      new_vaccinations_smoothed_per_mi    new_vaccinations_smoothed_per_million
   47      new_people_vaccinated_smoothed_p    new_people_vaccinated_smoothed_per_hundred
   64      excess_mortality_cumulative_abso    excess_mortality_cumulative_absolute
   67      excess_mortality_cumulative_per_    excess_mortality_cumulative_per_million

You can use this macro to help you figure out how to read the CSV file. https://github.com/sasutils/macros/blob/master/csv2ds.sas

Once you run it you can then save the data step it create and re-use to read future versions of the file.

data COVID;
  infile 'c:\downloads\owid-covid-data.csv' dlm=',' dsd truncover
    firstobs=2
  ;
  length iso_code $8 continent $13 location $32 date 8 total_cases 8
    new_cases 8 new_cases_smoothed 8 total_deaths 8 new_deaths 8
    new_deaths_smoothed 8 total_cases_per_million 8
    new_cases_per_million 8 new_cases_smoothed_per_million 8
    total_deaths_per_million 8 new_deaths_per_million 8
    new_deaths_smoothed_per_million 8 reproduction_rate 8 icu_patients 8
    icu_patients_per_million 8 hosp_patients 8 hosp_patients_per_million 8
    weekly_icu_admissions 8 weekly_icu_admissions_per_millio 8
    weekly_hosp_admissions 8 weekly_hosp_admissions_per_milli 8
    new_tests 8 total_tests 8 total_tests_per_thousand 8
    new_tests_per_thousand 8 new_tests_smoothed 8
    new_tests_smoothed_per_thousand 8 positive_rate 8 tests_per_case 8
    tests_units $15 total_vaccinations 8 people_vaccinated 8
    people_fully_vaccinated 8 total_boosters 8 new_vaccinations 8
    new_vaccinations_smoothed 8 total_vaccinations_per_hundred 8
    people_vaccinated_per_hundred 8 people_fully_vaccinated_per_hund 8
    total_boosters_per_hundred 8 new_vaccinations_smoothed_per_mi 8
    new_people_vaccinated_smoothed 8 new_people_vaccinated_smoothed_p 8
    stringency_index 8 population 8 population_density 8 median_age 8
    aged_65_older 8 aged_70_older 8 gdp_per_capita 8 extreme_poverty 8
    cardiovasc_death_rate 8 diabetes_prevalence 8 female_smokers 8
    male_smokers 8 handwashing_facilities 8 hospital_beds_per_thousand 8
    life_expectancy 8 human_development_index 8
    excess_mortality_cumulative_abso 8 excess_mortality_cumulative 8
    excess_mortality 8 excess_mortality_cumulative_per_ 8
  ;
  informat date yymmdd. ;
  format date yymmdd10. excess_mortality_cumulative_abso best18.
    excess_mortality_cumulative_per_ best18.
  ;
  label
    weekly_icu_admissions_per_millio='weekly_icu_admissions_per_million'
    weekly_hosp_admissions_per_milli='weekly_hosp_admissions_per_million'
    people_fully_vaccinated_per_hund='people_fully_vaccinated_per_hundred'
    new_vaccinations_smoothed_per_mi='new_vaccinations_smoothed_per_million'
    new_people_vaccinated_smoothed_p='new_people_vaccinated_smoothed_per_hundred'
    excess_mortality_cumulative_abso='excess_mortality_cumulative_absolute'
    excess_mortality_cumulative_per_='excess_mortality_cumulative_per_million'
  ;
  input iso_code -- excess_mortality_cumulative_per_ ;
run;

Re: Import data through data step

Reeza — Fri, 26 Nov 2021 22:26:41 GMT

More information definitely helps.

This reads in the file correctly, takes a few minutes to run (2mins on the server due to the GUESSINGROWS=MAX option).

Some variables seem empty but I'll leave you to verify that it's accurate from here.

Note that this used PROC IMPORT - if you switch to a data step you may avoid a lot of these issues. As @Kurt_Bremser identified some variable names are too long in there for SAS and are truncated (the log tells you this as well) so a data step would be faster and fix all of that. I would copy the import code from the log, remove the line numbers, adjust the code to have the names I want and re-run it if this was for a regular process.


filename rawData temp;

proc http
 url="https://covid.ourworldindata.org/data/owid-covid-data.csv"
 method="GET"
 out=RawData;
run;

proc import out=covidData file=rawData dbms=csv replace; guessingrows=max;
run;

data want;
set covidData;
keep 
location
date 
new_cases_per_million
new_deaths_per_million
weekly_hosp_admissions_per_milli
people_fully_vaccinated
stringency_index
population
;
run;

@Job04 wrote:

Data is from here: https://github.com/owid/covid-19-data/tree/master/public/data

I'm intrested only in these variables:

location

date new_cases_per_million

new_deaths_per_million

weekly_hosp_admissions_per_million

people_fully_vaccinated

stringency_index

population

Re: Import data through data step

Job04 — Mon, 29 Nov 2021 22:33:14 GMT

This imports everything but variables continent and location are messed up.

Re: Import data through data step

Reeza — Mon, 29 Nov 2021 23:24:10 GMT

Have you verified the contents of the CSV file?