topic Re: SELECTED REGION WISE in SAS Programming

SELECTED REGION WISE

BrahmanandaRao — Tue, 31 Mar 2020 09:45:14 GMT

	proc import datafile ="C:\Users\Anand\Desktop\covid19 updated_onMar31-2020.xlsx"
	out=covid19 
	dbms=xlsx;
	run;



 data covid19a;
set covid19;
where Reporting_Country_Territory_Area in ('Western Pacific Region' 'South-East Asia Region');
proc print;
run;

Hi guys

gentle remainder

but i didn't get proper solutions

please find below attachment and i want retrive information

two regions only

'Western Pacific Region' 'South-East Asia Region'

i want above two regions countries data

Re: SELECTED REGION WISE

andreas_lds — Tue, 31 Mar 2020 10:06:58 GMT

Are there any unexpected notes, warnings or errors in the log? If so, please post the log as text using "insert code" button.

Re: SELECTED REGION WISE

Kurt_Bremser — Tue, 31 Mar 2020 10:40:51 GMT

I stored your Excel file in my UE directory, and ran this:

proc import
  datafile ="/folders/myfolders/covid19 updated_onMar31-2020.xlsx"
  out=covid19 
  dbms=xlsx
  replace
;
run;

data covid19a;
set covid19;
where Reporting_Country_Territory_Area in ('Western Pacific Region' 'South-East Asia Region');
run;

proc print data=covid19a noobs;
run;

and I got the two observations that match your WHERE condition.

Re: SELECTED REGION WISE

BrahmanandaRao — Tue, 31 Mar 2020 12:00:25 GMT

Post output what you have got
I want all countries in both regions

Re: SELECTED REGION WISE

Kurt_Bremser — Tue, 31 Mar 2020 12:13:00 GMT

@BrahmanandaRao wrote:
Post output what you have got
I want all countries in both regions

With the current structure, this is not possible. It seems that you need to convert a report-style layout to a correct table:

proc import
  datafile ="/folders/myfolders/covid19 updated_onMar31-2020.xlsx"
  out=covid19 
  dbms=xlsx
  replace
;
run;

data covid19_expanded;
length region $47;
set covid19;
retain region;
if
  total_confirmed_cases = . /* "region" line */
then do;
  if reporting_country_territory_area ne "Territories**"
  /* otherwise the two "Territories**" groups form their own region */
  then region = reporting_country_territory_area;
  delete; /* we don't want this extra observation in the dataset */
end;
run;

data want;
set covid19_expanded;
where region in ('Western Pacific Region' 'South-East Asia Region');
run;

Re: SELECTED REGION WISE

quran — Sat, 14 Dec 2024 07:35:16 GMT

proc import datafile ="C:\Users\Anand\Desktop\covid19 updated_onMar31-2020.xlsx"
	out=covid19 
	dbms=xlsx;
run;
data covid19a;
  set covid19;
  where Reporting_Country_Territory_Area in ('Western Pacific Region', 'South-East Asia Region');
run;
proc print data=covid19a;
run;

This code will:

Import the data:
- Reads the Excel file covid19 updated_onMar31-2020.xlsx from the specified path.
- Creates a SAS dataset named covid19 from the imported data.
Filter the data:
- Creates a new dataset covid19a containing only the rows where the regionReporting_Country_Territory_Area is either 'Western Pacific Region' or 'South-East Asia Region.'.
Print the filtered data:
- Displays the contents of the covid19a dataset.

This refined code addresses the issue in the original code by correctly specifying the where clause to filter for the two desired regions.

Re: SELECTED REGION WISE

mkeintz — Sun, 15 Dec 2024 01:20:16 GMT

@quran

You may have missed the objective of the original poster, or perhaps didn't notice the structure of the data.

The OP wanted "every country" in the two specified regions. But unfortunately there is no Excel column (thus no imported SAS variable) that identifies the region for every observation. Instead, there are rows with the region identified in the variable Reporting_Country_Territory_Area. Luckily the word "Region" appears in this variable for all such rows and only those rows.

Following those rows are a series of countries (also identified in Reporting_Country_Territory_Area) that belong to the identified region.

Your code retrieves only the two rows that identify the desired regions, but none of the rows following them. That is what @Kurt_Bremser 's code addresses.

If you intend to make the subset for WANT directly from COVID19 imported dataset, then:

data want;
  set covid19 ;
  length region $47;
  retain region;

  if findw(Reporting_Country_Territory_Area,'Region') then region=reporting_country_territory_area;
  else if region in ('Western Pacific Region' 'South-East Asia Region') then output;
run;

Or you can just create the COVID19_EXPANNDED data set:

data covid19_expanded;
  set covid19 ;
  length region $47;
  retain region;

  if findw(Reporting_Country_Territory_Area,'Region') then region=reporting_country_territory_area;
  else output;
run;

Then you could use the WHERE filter that you propose.