question on choosing suitable proc for conditions

[Amali6]

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!

[ballardw]

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.

[Amali6]

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

[Kurt_Bremser]

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.

[Amali6]

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?

[Kurt_Bremser]

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.

[Amali6]

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

[Kurt_Bremser · Accepted solution]

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.

[Amali6]

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

[Kurt_Bremser]

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.

[Amali6]

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