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;
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?
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.
How does this answer @Kurt_Bremser 's questions?
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?
can Any one do this problem
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?
@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;
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.