topic Re: Handling missing values in SAS Procedures

Handling missing values

BrahmanandaRao — Sat, 21 Mar 2020 05:58:52 GMT

proc import datafile ='Filelocation'
out=covid19
dbms=xlsx;
run;
use attachment xlsx file

i am trying below code but it's not get correct ouput 
proc sql;
select * from Covid19
where Reporting_Country_Territory_Area IN( 'European Region'	'South-East Asia Region');
quit;

Hi Every one

How to Filter missing values in the below attachment

proc sql;
select * from Covid19
where Reporting_Country_Territory_Area IN( 'European Region'	'South-East Asia Region');
quit;

Re: Handling missing values

Kurt_Bremser — Fri, 20 Mar 2020 09:48:31 GMT

Please provide example data in self-contained data steps with datalines.

If external files are provided, add the code used to import them into SAS.

Which missing values do you need filtered? Do you want to replace them with something else, or do you want observations containing the missing values deleted from the resulting dataset?

Re: Handling missing values

BrahmanandaRao — Fri, 20 Mar 2020 09:56:08 GMT

Reporting Country_Territory_Area Total confirmecases Total confirmed new cases1 Total deaths Total new deaths1 Transmission classification Days since last reported case
Western Pacific Region
China 81174 58 3242 11 Local transmission 0
Republic of Korea 8413 93 84 3 Local transmission 0
Japan 873 44 29 1 Local transmission 0
Malaysia 673 120 2 2 Local transmission 0
Australia 510 96 6 1 Local transmission 0
Singapore 313 47 0 0 Local transmission 0
Philippines 187 0 14 2 Local transmission 1
Viet Nam 66 5 0 0 Local transmission 0
Brunei Darussalam 56 2 0 0 Local transmission 0
Cambodia 35 11 0 0 Local transmission 0
New Zealand 20 9 0 0 Local transmission 0
Mongolia 5 1 0 0 Imported cases only 0
Territories**
Guam 5 2 0 0 Local transmission 0
French Polynesia 3 0 0 0 Imported cases only 3
European Region
Italy 35713 4207 2978 473 Local transmission 0
Spain 13716 2538 598 107 Local transmission 0
France 9043 0 244 0 Local transmission 1
Germany 8198 1042 13 0 Local transmission 0
Switzerland 3010 353 21 2 Local transmission 0
The United Kingdom 2630 672 103 0 Local transmission 0
Netherlands 2051 0 58 0 Local transmission 1
Austria 1646 314 4 1 Local transmission 0
Belgium 1486 0 14 0 Local transmission 1
Norway 1423 115 3 0 Local transmission 0

Re: Handling missing values

Kurt_Bremser — Fri, 20 Mar 2020 10:02:02 GMT

You have not provided a working data step with datalines, so I still have only a very rudimentary idea what your data looks like. And you have not answered my questions.

Re: Handling missing values

BrahmanandaRao — Fri, 20 Mar 2020 10:04:45 GMT

data covid19;
infile datalines;
input Reporting Country_Territory_Area Total confirmecases Total confirmed new cases1 Total deaths Total new deaths1 Transmission classification Days since last reported case
Western Pacific Region ;
cards;
China 81174 58 3242 11 Local transmission 0
Republic of Korea 8413 93 84 3 Local transmission 0
Japan 873 44 29 1 Local transmission 0
Malaysia 673 120 2 2 Local transmission 0
Australia 510 96 6 1 Local transmission 0
Singapore 313 47 0 0 Local transmission 0
Philippines 187 0 14 2 Local transmission 1
Viet Nam 66 5 0 0 Local transmission 0
Brunei Darussalam 56 2 0 0 Local transmission 0
Cambodia 35 11 0 0 Local transmission 0
New Zealand 20 9 0 0 Local transmission 0
Mongolia 5 1 0 0 Imported cases only 0
Territories**
Guam 5 2 0 0 Local transmission 0
French Polynesia 3 0 0 0 Imported cases only 3
European Region
Italy 35713 4207 2978 473 Local transmission 0
Spain 13716 2538 598 107 Local transmission 0
France 9043 0 244 0 Local transmission 1
Germany 8198 1042 13 0 Local transmission 0
Switzerland 3010 353 21 2 Local transmission 0
The United Kingdom 2630 672 103 0 Local transmission 0
Netherlands 2051 0 58 0 Local transmission 1
Austria 1646 314 4 1 Local transmission 0
Belgium 1486 0 14 0 Local transmission 1
Norway 1423 115 3 0 Local transmission 0

;
run;

Re: Handling missing values

PaigeMiller — Fri, 20 Mar 2020 10:41:29 GMT

How does this answer @Kurt_Bremser 's questions?

Re: Handling missing values

Kurt_Bremser — Fri, 20 Mar 2020 14:27:01 GMT

You still have not answered my questions, and this is the log from your data step:

27         data covid19;
28         infile datalines;
29         input Reporting Country_Territory_Area Total confirmecases Total confirmed new cases1 Total deaths Total new deaths1
29       ! Transmission classification Days since last reported case
30         Western Pacific Region ;
31         cards;

NOTE: Invalid data for Reporting in line 32 1-5.
NOTE: Invalid data for confirmed in line 32 24-28.
NOTE: Invalid data for new in line 32 30-41.
NOTE: Invalid data for Total in line 33 1-8.
NOTE: Invalid data for deaths in line 33 10-11.
NOTE: Invalid data for Total in line 33 13-17.
NOTE: Invalid data for Days in line 33 32-36.
NOTE: Invalid data for since in line 33 38-49.
NOTE: Invalid data for reported in line 34 1-5.
REGEL:     ----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8----+----9----+----0                     
34         Japan 873 44 29 1 Local transmission 0
NOTE: Invalid data errors for file CARDS occurred outside the printed range.
NOTE: Increase available buffer lines with the INFILE n= option.
Reporting=. Country_Territory_Area=81174 Total=. confirmecases=3242 confirmed=. new=8413 cases1=0 deaths=. deaths1=93
Transmission=84 classification=3 Days=. since=. last=0 reported=. case=873 Western=44 Pacific=29 Region=1 _ERROR_=1 _N_=1
NOTE: Invalid data for Reporting in line 35 1-8.
NOTE: Invalid data for confirmed in line 35 22-26.
NOTE: Invalid data for new in line 35 28-39.
NOTE: Invalid data for Total in line 36 1-9.
NOTE: Invalid data for Transmission in line 36 22-26.
NOTE: Invalid data for classification in line 36 28-39.
NOTE: Invalid data for since in line 37 1-9.
NOTE: Invalid data for Pacific in line 37 22-26.
NOTE: Invalid data for Region in line 37 28-39.
2                                                          Das SAS System                               09:12 Friday, March 20, 2020

REGEL:     ----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8----+----9----+----0                     
37         Singapore 313 47 0 0 Local transmission 0
NOTE: Invalid data errors for file CARDS occurred outside the printed range.
NOTE: Increase available buffer lines with the INFILE n= option.
Reporting=. Country_Territory_Area=673 Total=96 confirmecases=2 confirmed=. new=6 cases1=0 deaths=510 deaths1=1 Transmission=.
classification=. Days=0 since=. last=313 reported=47 case=0 Western=0 Pacific=. Region=. _ERROR_=1 _N_=2
NOTE: Invalid data for Reporting in line 38 1-11.
NOTE: Invalid data for confirmed in line 38 24-28.
NOTE: Invalid data for new in line 38 30-41.
NOTE: Invalid data for Total in line 39 1-4.
NOTE: Invalid data for deaths in line 39 6-8.
NOTE: Invalid data for classification in line 39 19-23.
NOTE: Invalid data for Days in line 39 25-36.
NOTE: Invalid data for last in line 40 1-6.
NOTE: Invalid data for reported in line 40 8-17.
40         Brunei Darussalam 56 2 0 0 Local transmission 0
NOTE: Invalid data errors for file CARDS occurred outside the printed range.
NOTE: Increase available buffer lines with the INFILE n= option.
Reporting=. Country_Territory_Area=187 Total=66 confirmecases=14 confirmed=. new=5 cases1=1 deaths=. deaths1=0 Transmission=0
classification=. Days=. since=0 last=. reported=. case=56 Western=2 Pacific=0 Region=0 _ERROR_=1 _N_=3
NOTE: Invalid data for Reporting in line 41 1-8.
NOTE: Invalid data for confirmed in line 41 20-24.
NOTE: Invalid data for new in line 41 26-37.
NOTE: Invalid data for Total in line 42 1-3.
NOTE: Invalid data for deaths in line 42 5-11.
NOTE: Invalid data for classification in line 42 22-26.
NOTE: Invalid data for Days in line 42 28-39.
NOTE: Invalid data for last in line 43 1-8.
NOTE: Invalid data for Region in line 43 18-25.

If you are not willing to spend at least a little effort (and test your own data step), why should we?

Re: Handling missing values

BrahmanandaRao — Sat, 21 Mar 2020 10:53:33 GMT

can Any one do this problem

Re: Handling missing values

ghosh — Sat, 21 Mar 2020 19:14:53 GMT

yes, sure but could you fix your input statement first. If there are spaces in the c=variable name, re-write them as 'name with space'n otherwise the vars do not match the data.

Also, what's the question?

Re: Handling missing values

Patrick — Sun, 22 Mar 2020 23:20:54 GMT

@BrahmanandaRao wrote:

can Any one do this problem

Below code will read the data from the Excel - which is messy.

Not really sure what the problem is now. You get missing values in the SAS table because there are missing values/cells in the Excel sheet.

options validvarname=v7;
proc import 
  datafile ='~/test/covid19 updated_onMar20-2020.xlsx'
  out=covid19
  dbms=xlsx
  replace;
  getnames=yes;
run;

proc sql;
select * 
from Covid19
where upcase(Reporting_Country_Territory_Area) IN('EUROPEAN REGION','SOUTH-EAST ASIA REGION');
quit;

And here an option how you can at least delete observations where all variables are missing.

proc sql noprint;
  select count(name) into :n_vars trimmed
  from dictionary.columns
  where libname='WORK' and memname='COVID19'
  ;
quit;
data covid19;
  set covid19;
  if cmiss(of _all_)=&n_vars then delete;
run;

Re: Handling missing values

BrahmanandaRao — Mon, 23 Mar 2020 13:38:02 GMT

I want below out put

Reporting Country_Territory_Area Total confirmecases Total confirmed new cases1 Total deaths Total new deaths1 Transmission classification Days since last reported case
Western Pacific Region

Armenia 84 32 0 0 Local transmission 0
Croatia 81 16 0 0 Local transmission 0
Latvia 71 11 0 0 Imported cases only 0
Albania 59 2 2 1 Local transmission 0
Cyprus 58 25 0 0 Local transmission 0
Hungary 58 8 1 0 Local transmission 0
Malta 48 10 0 0 Imported cases only 0
Belarus 46 10 0 0 Local transmission 0
Georgia 38 4 0 0 Imported cases only 0
Bosnia and Herzegovina 36 7 0 0 Local transmission 0
Kazakhstan 36 3 0 0 Imported cases only 0
North Macedonia 36 5 0 0 Local transmission 0
Republic of Moldova 36 0 0 0 Local transmission 1
Azerbaijan 34 13 1 1 Imported cases only 0
Lithuania 26 1 0 0 Imported cases only 0
Liechtenstein 25 18 0 0 Imported cases only 0
Ukraine 16 7 2 0 Local transmission 0
Uzbekistan 16 0 0 0 Imported cases only 1
Monaco 9 0 0 0 Under investigation 2
Kyrgyzstan 3 3 0 0 Under investigation 0
Montenegro 2 0 0 0 Imported cases only 1
Holy See 1 0 0 0 Under investigation 12
Territories**
Faroe Islands 58 11 0 0 Imported cases only 0
Andorra 39 23 0 0 Imported cases only 0
Gibraltar 8 5 0 0 Under investigation 0
Jersey 5 0 0 0 Imported cases only 1
Greenland 2 0 0 0 Under investigation 1
Guernsey 1 0 0 0 Imported cases only 9
South-East Asia Region
Indonesia 227 55 19 14 Local transmission 0
Thailand 212 35 1 0 Local transmission 0
India 151 14 3 0 Local transmission 0
Sri Lanka 42 13 0 0 Local transmission 0
Maldives 13 0 0 0 Local transmission 3
Bangladesh 10 2 0 0 Local transmission 0
Bhutan 1 0 0 0 Imported cases only 12
Nepal 1 0 0 0 Imported cases only 55

Re: Handling missing values

ghosh — Mon, 23 Mar 2020 14:14:36 GMT

As I mentioned earlier, your list of variables does not match the data you have provided. How would we know what data is missing

Re: Handling missing values

BrahmanandaRao — Mon, 23 Mar 2020 16:11:26 GMT

Filter region wise