topic Re: Incorrect Table in New SAS User

Incorrect Table

JoshuaG — Thu, 20 Oct 2022 03:53:14 GMT

Data passenger;
infile cards missover;
input @1 Rank $2. @2 Name $40. @3 Code $3. @4 City $12. @5 State $2. @6 yr2014 COMMA10.2 @7 yr2013 COMMA10.2 @8 yr2012 COMMA10.2 @9 yr2011 COMMA10.2;  
cards;
1 Hartsfield CJackson Atlanta International AirportATL Atlanta GA 46604273 45308407 45798809 44414121
2 Los Angeles International AirportLAX Los AngelesCA 34314197 32425892 31326268 30528737
3 O'Hare International AirportORD Chicago IL 33686811 32317835 32171743 31892301
4 Dallas/Fort Worth International AirportDFW Dallas/Fort WorthTX 30766940 29038128 28022877 27518358
5 John F. Kennedy International AirportJFK New York NY 26244928 25036358 24520943 23664830
6 Denver International AirportDEN Denver CO 26000591 25496885 25799832 25667499
7 San Francisco International AirportSFO San FranciscoCA 22756008 21704626 21284224 20038679
8 Charlotte Douglas International AirportCLT Charlotte NC 21542277 21346601 20032426 19022535
9 McCarran International AirportLAS Las Vegas NV 20551016 19946179 19941173 19854759
10 Phoenix Sky Harbor International AirportPHX Phoenix AZ 20344867 19525109 19556189 19750306
;
run;

proc sql;
select*
from passenger;
quit;

Any help as to why my table isnt matching the correct output on the right(of the screenshot) would be greatly appreciated!

Re: Incorrect Table

JoshuaG — Thu, 20 Oct 2022 03:55:38 GMT

Here is the instructions for context:

STAT 325 HOMEWORK 5
sql
Data description: Here are two datasets about top 10 busiest airports in the US. One is based on passengers’
statistics from 2011 to 2014 (passenger.csv). The other is based on cargo statistics (cargo.csv).
Passenger.csv:
Column 1: Rank;
Column 2: Name; (Airport name)
Column 3: Code; (Airport IATA Code)
Column4: City;
Column5: State;
Column6: Number of passengers in 2014;
Column7: Number of passengers in 2013;
Column8: Number of passengers in 2012;
Column9: Number of passengers in 2011;

Cargo.csv:
Column 1: Rank;
Column 2: Name; (Airport name)
Column 3: Location;
Column 4: Code; (Airport IATA Code)
Column 5: Cargo weight in 2014; (unit in pounds)
Column 6: Cargo weight in 2013; (unit in pounds)
Column 7: Cargo weight in 2012; (unit in pounds)
Column 8: Cargo weight in 2011; (unit in pounds)
Source:
https://en.wikipedia.org/wiki/List_of_the_busiest_airports_in_the_United_States#10_busiest_US_airports_by_i
nternational_passenger_traffic

Note: Please specify question mark as a comment before your program (eg /*question 1*/):

1 Read in the passenger.csv data file and apply COMMAw. format for the number of passenger;

Re: Incorrect Table

JoshuaG — Thu, 20 Oct 2022 05:46:35 GMT

I have no clue as to what is wrong with my code to be getting this incorrect table output.

Re: Incorrect Table

Kurt_Bremser — Thu, 20 Oct 2022 06:38:42 GMT

The @ indicator points to character columns, not "logical" columns.

In your other post, you talk about csv files, but there are no commas in the datalines.

If you need to read from csv files, open them with a text editor (not with Excel!) and copy/paste the contents into a code box here.

Re: Incorrect Table

JoshuaG — Thu, 20 Oct 2022 06:46:59 GMT

Updated code:

/*Question1*/
Data passenger;
infile '/home/u62108616/sasuser.v94/S325/passenger.csv' ; 
input @1 rank $2. @2 name $50. @3 code $3. @4 city $50. @5 state $2. @6 yr2014 @7 yr2013 @8 yr2012 @9 yr2011;
Format yr2014 COMMA15.0 yr2013 COMMA15.0 yr2012 COMMA15.0 yr2011 COMMA15.0;
run;

/*Question2*/
proc sql;
select*
from passenger;
quit;

Re: Incorrect Table

Kurt_Bremser — Thu, 20 Oct 2022 07:51:22 GMT

Quote from myself:

If you need to read from csv files, open them with a text editor (not with Excel!) and copy/paste the contents into a code box here.

We need to see your raw data.

Re: Incorrect Table

JoshuaG — Thu, 20 Oct 2022 12:42:37 GMT

1 Hartsfield CJackson Atlanta International AirportATL Atlanta GA 46604273 45308407 45798809 44414121
2 Los Angeles International AirportLAX Los AngelesCA 34314197 32425892 31326268 30528737
3 O'Hare International AirportORD Chicago IL 33686811 32317835 32171743 31892301
4 Dallas/Fort Worth International AirportDFW Dallas/Fort WorthTX 30766940 29038128 28022877 27518358
5 John F. Kennedy International AirportJFK New York NY 26244928 25036358 24520943 23664830
6 Denver International AirportDEN Denver CO 26000591 25496885 25799832 25667499
7 San Francisco International AirportSFO San FranciscoCA 22756008 21704626 21284224 20038679
8 Charlotte Douglas International AirportCLT Charlotte NC 21542277 21346601 20032426 19022535
9 McCarran International AirportLAS Las Vegas NV 20551016 19946179 19941173 19854759
10 Phoenix Sky Harbor International AirportPHX Phoenix AZ 20344867 19525109 19556189 19750306

Re: Incorrect Table

JoshuaG — Thu, 20 Oct 2022 12:49:05 GMT

I was able to fix this issue, however, I am stuck on this portion now:

3) Find the average number of passenger in the top ten airport in 2014, label it as ‘Average passenger in
2014’ in the format of COMMAw. ;

4) Find the average passenger volume in Atlanta airport, label it as “Average pasenger in ATL”, and use
the same format as the previous question;

5) Find the maximum passenger volume in 2011, label it as “Max volume 2011”, and use the same format
as the previous question;

Code:

/*Question3*/
proc sql;
select avg(yr2014) as 'Average passenger in 2014' format =COMMA10.2
from passenger;
quit;

/*Question4*/
proc sql;
select avg(ATL) as Average passenger in ATL format =COMMA10.2
from passenger;
quit;

/*Question5*/
proc sql;
select max(yr2011) as Max Volume 2011 =COMMA10.2
from passenger;
quit;

Output im looking for:

Re: Incorrect Table

JoshuaG — Thu, 20 Oct 2022 12:55:13 GMT

Here is the error message that pops up for each of these questions:

ERROR 22-322: Expecting a name.

I have tried using and not using quotations, here is the error for when I dont put the title in quotations:

ERROR 22-322: Syntax error, expecting one of the following: a quoted string, ',', AS, FORMAT, FROM, INFORMAT, INTO, LABEL, LEN,

LENGTH, TRANSCODE.

ERROR 202-322: The option or parameter is not recognized and will be ignored.

Re: Incorrect Table

Tom — Thu, 20 Oct 2022 17:58:17 GMT

Your SQL code is NOT trying to attach a LABEL to the variable. Because you used the AS keyword it is trying to use a variable name that starts with a quote. You cannot use a variable NAME that starts with a quote. Normal variable names can only contain digits, letters and underscores and cannot start with a digit. If your variable name does not follow those rules then you need to use a name literal in the code.

So you could correct the code many ways.

First you could fix the SQL so that you are attaching a label instead. Using one of these methods

select max(age) 'Max Age' from sashelp.class;
select max(age) 'Max Age' as max_age from sashelp.class;
select max(age) as max_age 'Max Age' from sashelp.class;
select max(age) as max_age label='Max Age' from sashelp.class;
select max(age) as max_age label 'Max Age' from sashelp.class;

Or if you have the VALIDVARNAME option set to ANY (and the column header you want to print is 32 bytes or less) then you could use a name literal for the variable name.

select max(age) as 'Max Age'n from sashelp.class;

But since the instruction explicitly used the term LABEL I would go with one of those solutions.

Re: Incorrect Table

JoshuaG — Thu, 20 Oct 2022 13:17:14 GMT

For Question 3and Question 5:

What should I do in order to get the commas that are produced in the intended output?

Code:

/*Question3*/
proc sql;
select avg(yr2014) as Average_passenger_in_2014 label = 'Average passenger in 2014' format = COMMA8.2
from passenger;
quit;

/*Question5*/
proc sql;
select max(yr2011) as Max_Volume_2011 label = 'Max Volume 2011' format = COMMA8.2
from passenger;
quit;

Current Output:

Re: Incorrect Table

JoshuaG — Thu, 20 Oct 2022 13:19:32 GMT

Question 4:

How should I go about this error given what this question is being asked for?

Code:

/*Question4*/
proc sql;
select avg(ATL) as Average_passenger_in_ATL label = 'Average passenger in ATL' format = COMMA10.2
from passenger;
quit;

Error:
 ERROR: The AVG summary function requires a numeric argument.
 ERROR: The following columns were not found in the contributing tables: ATL.

Re: Incorrect Table

JoshuaG — Thu, 20 Oct 2022 13:50:11 GMT

Ive been able to fix questions 3-5.

I am having some issues now with question 6:

6) Concatenate the variable columns of city and state as a new variable called Location, with comma
separated in between, and output the variables of name, code and location only;

Code:

/*Question6*/
proc sql;
select*,name, code, city||","||state as Location
from passenger;
quit;

Expected Output:

My output:

Re: Incorrect Table

Tom — Thu, 20 Oct 2022 14:10:57 GMT

You have more variables in your output because you told SQL to include ALL of the original variables by using * in the list of variables in the SELECT.

I doubt that LOCATION looks as nice as that. You should have many extra spaces before the comma. That is because you did not remove the spaces SAS uses to fill up the CITY variable. SAS character strings are fixed length. Normally the trailing spaces are ignored in comparisons, but they are still there unless you do something to remove them.

trim(city)||","||state

personally I would add a space after the comma also.

SAS has a series of CAT...() functions that make it much easier. CATX() is the one to use for this

catx(', ',city,state)

Re: Incorrect Table

JoshuaG — Thu, 20 Oct 2022 14:25:47 GMT

I was able to fix question 6 using trim. I wanted to see if question 7 looks good:

7) Read in the cargo.csv data file and output the variables in order of rank, name, code, location, year
from 2014 to 2011;

Cargo File:

1 Memphis International AirportMemphis, TennesseeMEM ######## ######## ######## ########
2 Ted Stevens Anchorage International AirportAnchorage, AlaskaANC ######## ######## ######## ########
3 Louisville International AirportLouisville, KentuckySDF ######## ######## ######## ########
4 O'Hare International AirportChicago, IllinoisORD ######## ######## ######## ########
5 Miami International AirportMiami, FloridaMIA ######## ######## ######## ########
6 Indianapolis International AirportIndianapolis, IndianaIND ######## ######## ######## ########
7 Los Angeles International AirportLos Angeles, CaliforniaLAX ######## ######## ######## ########
8 Cincinnati/Northern Kentucky International AirportCincinnati, OhioCVG ######## ######## ######## ########
9 John F. Kennedy International AirportNew York, New YorkJFK ######## ######## ######## ########
10 Dallas/Fort Worth International AirportFort Worth, TexasDFW ######## ######## ######## ########

Code:

/*Question7*/
Data cargo;
length rank $2. name $50. location $50. code $3.;
infile '/home/u62108616/sasuser.v94/S325/cargo.csv' dlm = ','; 
input rank $ name $ location $ code $ wt2014 wt2013 wt2012 wt2011;
Format wt2014 COMMA15.0 wt2013 COMMA15.0 wt2012 COMMA15.0 wt2011 COMMA15.0;
run;

Re: Incorrect Table

JoshuaG — Thu, 20 Oct 2022 14:29:24 GMT

For the last two questions, I was stuck on how to start:

😎 Update the name variable by adding the airport code column after the first word of airport name, keep
a space in between.

9)Split the location variables into two columns, city and state; output the airport name (the variable we
used before updating), city and state variables only.

Do we have to use merge for question 8? What does an example of this look like?

How do I go about writing question 9?

Re: Incorrect Table

Tom — Thu, 20 Oct 2022 14:39:01 GMT

That does not look like a CSV file. It does not have commas between the values. Perhaps they are using TAB as the delimiter instead? You really should always use the DSD option when reading delimited files. And always use the TRUNCOVER option when all of the data for one observation is on one line of the text file being read.

Reading RANK as a character variable will not let it sort properly. '12' will sort before '2' since it starts with the digit 1 which comes before the digit 2.

Re: Incorrect Table

JoshuaG — Thu, 20 Oct 2022 15:21:07 GMT

Was able to implement those fixes for question 7. Still stuck on how to approach question 8 and question 9

Re: Incorrect Table

Kurt_Bremser — Fri, 21 Oct 2022 06:33:40 GMT

@JoshuaG wrote:

I was able to fix question 6 using trim. I wanted to see if question 7 looks good:

7) Read in the cargo.csv data file and output the variables in order of rank, name, code, location, year
from 2014 to 2011;

Cargo File:

1 Memphis International AirportMemphis, TennesseeMEM ######## ######## ######## ########
2 Ted Stevens Anchorage International AirportAnchorage, AlaskaANC ######## ######## ######## ########
3 Louisville International AirportLouisville, KentuckySDF ######## ######## ######## ########
4 O'Hare International AirportChicago, IllinoisORD ######## ######## ######## ########
5 Miami International AirportMiami, FloridaMIA ######## ######## ######## ########
6 Indianapolis International AirportIndianapolis, IndianaIND ######## ######## ######## ########
7 Los Angeles International AirportLos Angeles, CaliforniaLAX ######## ######## ######## ########
8 Cincinnati/Northern Kentucky International AirportCincinnati, OhioCVG ######## ######## ######## ########
9 John F. Kennedy International AirportNew York, New YorkJFK ######## ######## ######## ########
10 Dallas/Fort Worth International AirportFort Worth, TexasDFW ######## ######## ######## ########

Code:

/*Question7*/
Data cargo;
length rank $2. name $50. location $50. code $3.;
infile '/home/u62108616/sasuser.v94/S325/cargo.csv' dlm = ','; 
input rank $ name $ location $ code $ wt2014 wt2013 wt2012 wt2011;
Format wt2014 COMMA15.0 wt2013 COMMA15.0 wt2012 COMMA15.0 wt2011 COMMA15.0;
run;

What you show is not a csv file (no commas). The fact that all the numbers are replaced by ######## lets me suspect you did not follow my specific request to use a TEXT EDITOR to open the file.