topic Re: question on choosing suitable proc for conditions in SAS Procedures

question on choosing suitable proc for conditions

Amali6 — Thu, 14 May 2020 00:28:20 GMT

Hello

I wanted to create UI using proc pmenu and associate it with a suitable proc, based on my conditions. For example i want to select only one hotel and a specific year from a specific country from my dataset and see output on the list of observations satisfies my condition. I tried proc sql but its not working. Any suggestions please which proc would be suitable to show only these conditions in the result window?

Thanks in advance!

Re: question on choosing suitable proc for conditions

ballardw — Thu, 14 May 2020 00:33:17 GMT

Data

Conditions

Without know the sort of conditions and variable types involved not much can be suggested.

And if Pmenu works you may consider the Window / macro %window for entering things.

Have you got a manually written Proc SQL or data step that works? If not then it is not time to worry about Pmenu or Window controls yet.

If using Enterprise Guide then Prompts might be in the process as well.

"Not working" is awful vague.

Are there errors in the log?: Post the code and log in a code box opened with the <> to maintain formatting of error messages.

No output? Post any log in a code box.

Unexpected output? Provide input data in the form of data step code pasted into a code box, the actual results and the expected results. Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the <> icon or attached as text to show exactly what you have and that we can test code against.

Re: question on choosing suitable proc for conditions

Kurt_Bremser — Thu, 14 May 2020 06:57:49 GMT

To build something like this today, you create the report/query/whatever in Enterprise Guide, create user prompts that get the required condition values, use them in the code (values from prompts are supplied to the code as macro variables), and then convert the code to a stored process.

Working with Menus created by PROC PMENU requires a Base SAS on the user's desktop, or a remote Base SAS with a graphical UI (usually, Base SAS running on UNIX with X Windows).

Stored processes require only a browser on the user's side.

Re: question on choosing suitable proc for conditions

Amali6 — Thu, 14 May 2020 11:29:35 GMT

Actually I wanted to view only the user given information from the dataset. I have tried the following code but i am not sure whether am i right here. This is the code i tried. I will explain my criteria here, from my dataset hotel.Hotel_bookings i wanted to filter only people who went to Resort hotel in 2015 from the country GBR. Also i have a variable reservation_status, from this i wanted to filter only people who check-out from this hotel which will show they are the confirmed bookings. I am new to sas so i am not very sure how to give this conditions. Please help

options nodate pageno=1 linesize=80 pagesize=60;
proc sql;
create view hotel.main2 as
select hotel,arrival_date_year,country
where (hotel='Resort hotel'contains arrival_date_year=2015)
from hotel.Hotel_bookings
having country='GBR';
title'People from GBR to resort hotel in 2015';
quit;

#Log report#

 options nodate pageno=1 linesize=80 pagesize=60;
21   proc sql;
22   create view hotel.main2 as
23   select hotel,arrival_date_year,country
24   where (hotel='Resort hotel'contains arrival_date_year=2015)
     -----        --------------
     22           49
     76
ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, (, *, **,
              +, ',', -, /, <, <=, <>, =, >, >=, ?, AND, BETWEEN, CONTAINS, EQ,
              EQT, GE, GET, GT, GTT, LE, LET, LIKE, LT, LTT, NE, NET, OR, ^=,
              |, ||, ~=.

NOTE 49-169: The meaning of an identifier after a quoted string might change in a future SAS release.  Inserting white space between a quoted string and the succeeding identifier is recommended.

ERROR 76-322: Syntax error, statement will be ignored.

25   from hotel.main1
26   having country='GBR';
27   title'People from GBR to resort hotel in 2015';
28   quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.03 seconds
      cpu time            0.03 seconds

Re: question on choosing suitable proc for conditions

Kurt_Bremser — Thu, 14 May 2020 12:08:54 GMT

Structured code is easier to read; also keep global statements outside of steps, for better readability.

See this:

%let country=GBR;
%let hotel=Resort hotel;
%let year=2015;

title "People from &country. to &hotel. in &year.";

proc sql;
create view hotel.main2 as
  select
    hotel,
    arrival_date_year,
    country
  from hotel.Hotel_bookings
  where
    hotel = "&hotel." and
    arrival_date_year = &year. and
    country = "&country."
;
quit;

Once that works, you can populate the macro variables from user prompts.

Re: question on choosing suitable proc for conditions

Amali6 — Thu, 14 May 2020 12:09:26 GMT

Hi,
Since i am a student I have been installed sas 9.4 by my university on my laptop. I am just wondering how to filter the conditions mentioned in my post on any proc.. could you suggest me how to proceed please...

Thanks

Re: question on choosing suitable proc for conditions

Amali6 — Thu, 14 May 2020 12:16:58 GMT

NOTE: SQL view HOTEL.MAIN2 has been defined.
   quit;

After i run the code i got this message in log. And i can see main2 created in my explorer inside hotel library. But when i opened the main2 it says Data set has 0 observations showing in a dialog box. Only the column names are created inside main2.

Is this the correct output?

Re: question on choosing suitable proc for conditions

Kurt_Bremser — Thu, 14 May 2020 12:28:48 GMT

Note that the view itself does not have an observation count, the observations are determined on demand when the view is actually executed.

If you open the view like a dataset and see only column headers, then your WHERE condition has selected no observations. Maxim 3: Know Your Data. Inspect your dataset for the actual contents of the variables used in the WHERE. Take care of upper/lowercase specifically.

Re: question on choosing suitable proc for conditions

Amali6 — Thu, 14 May 2020 14:52:52 GMT

I dont know why there is no observations inside the main2, i have checked my dataset the variables i have declared in where statement is the same how it looks in the dataset.

The variables are included in the image. Where to correct to write the observations in main2, if you could help please...

Thanks

Re: question on choosing suitable proc for conditions

Kurt_Bremser — Thu, 14 May 2020 14:57:01 GMT

Take care of upper/lowercase specifically

Correct spelling in conditions is of the utmost importance.

"Resort hotel" is not equal to "Resort Hotel" !!

This is another reason why we insist on proper posting of example data. A data step with datalines leaves no doubts about variable attributes, type, formats, and contents.

Re: question on choosing suitable proc for conditions

Amali6 — Thu, 14 May 2020 15:15:55 GMT

Thank you very much now i got all observations under main2. SO now can i create the UI using proc pmenu and connect the the UI and sql file using the name menu2. Whether am i right?

Thanks for the help i really appreciate