Incorrect Table

[JoshuaG]

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!

[JoshuaG]

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;

[JoshuaG]

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

[Kurt_Bremser]

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.

[JoshuaG]

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;

[Kurt_Bremser]

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.

[JoshuaG]

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

[JoshuaG]

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: 

[JoshuaG]

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.

[Tom · Accepted solution]

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.

[JoshuaG]

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:

[JoshuaG]

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.

[JoshuaG]

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:

[Tom]

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)

[JoshuaG]

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;