Proc Sql: Creating New Table with Format

[talzy7]

I have succesfully created a table using a query in proc sql. The issue I am having is when I try and give my variables in my select statement a format (i.e. select salary format = comma10.2) the format is not being recognized. Any ideas as to why my format is being ignored?

[PaigeMiller]

Show us the LOG of the entire PROC SQL. Click on the {i} icon and paste the code into the window that appears — DO NOT SKIP THIS STEP.

Why do you say the format is being ignored? What do you see that indicates the format is being ignored? Show us.

[talzy7]

 
 1          OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
 70         
 71         
 72         libname  orion "/courses/d0f434e5ba27fe300/sql" access=readonly;
 NOTE: Libref ORION refers to the same physical library as _TEMP5.
 NOTE: Libref ORION was successfully assigned as follows: 
       Engine:        V9 
       Physical Name: /courses/d0f434e5ba27fe300/sql
 73         
 74         proc sql;
 75         create table work.Direct_Compensation as
 76         select distinct o.Employee_ID, catx('',First_Name, Last_Name) as Name, substr(Job_Title,12,3) as level, Salary informat =
 76       !  comma8.2 format = comma8.2, sum(Total_Retail_Price)*0.15 as commission  format = comma8.2, calculated commission +
 76       ! salary as Direct_Compensation  format = comma8.2
 77         from orion.Order_Fact as o , orion.Sales as s
 78         where o.Employee_ID=s.Employee_ID and year(Order_Date)=2007 and Job_Title not contains 'Manager'
 79         group by o.Employee_ID
 80         ;
 NOTE: The query requires remerging summary statistics back with the original data.
 NOTE: Table WORK.DIRECT_COMPENSATION created, with 44 rows and 6 columns.
 
 81         
 82         select *
 83         from work.Direct_Compensation
 84         ;
 85         quit;
 NOTE: PROCEDURE SQL used (Total process time):
       real time           0.07 seconds
       user cpu time       0.07 seconds
       system cpu time     0.00 seconds
       memory              20660.89k
       OS Memory           50108.00k
       Timestamp           02/12/2019 01:07:35 PM
       Step Count                        304  Switch Count  3
       Page Faults                       0
       Page Reclaims                     779
       Page Swaps                        0
       Voluntary Context Switches        25
       Involuntary Context Switches      0
       Block Input Operations            0
       Block Output Operations           288
       
 
 86         
 87         
 88         OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
 100 

Above is the log file. When adding a query to list the data in the table the format is not present in the output.

[PaigeMiller]

You didn't answer this question that I asked earlier. You can't say "it doesn't seem to work" without explaining.

Why do you say the format is being ignored? What do you see that indicates the format is being ignored? Show us.

[talzy7]

When I create a query to list the data in the table, the variables i formatted in the table do not appear formatted in the output.

[PaigeMiller]

Show us

Also, why do you need a query, what about PROC PRINT? Or Viewtable?

[talzy7]

Unfortunately, it is a requirement in my assignment. The output is below. As you can see the comma10.2 format is not being recognized for the variables Salary, Commission, and Direct_Compensation.

[PaigeMiller · Accepted solution]

Your code shows comma8.2 (not 10.2)

The format is not wide enough to put commas, all the digits (there are 7 of them) and a decimal point. You can't fit all of that into a width of 8.

Comma10.2 probably would work, comma12.2 would definitely work.

[talzy7]

Oh my goodness, such a silly mistake. Thank you!

[RW9]

"When I create a query to list the data in the table" - do you mean the lst output from proc sql?  Use proc report (or other reporting function) to output the data to a particular file format.  List output is just old plain text dump so doesn't have anything fancy.  I mean you could put() numeric into a format rather than applying a format, but why, if you want to look at the data in a nice format use the appropriate procedures.

[RW9]

I don't think you can apply format to a variable which already exists.  Have you tried:

select  oldvar as newvar length=20 format=$20 label="Some label"

So oldvar is what comes in, newvar would be name of variable going out.  Can be the same name, but needs to have the <var> as <var>

[talzy7]

I have tried this within the create table environment and it doesn't seem to work. If this were the case wouldn't the variable commission appear formatted?