topic Re: Proc Sql: Creating New Table with Format in SAS Studio

Proc Sql: Creating New Table with Format

talzy7 — Tue, 12 Feb 2019 13:16:15 GMT

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?

Re: Proc Sql: Creating New Table with Format

PaigeMiller — Tue, 12 Feb 2019 13:22:57 GMT

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.

Re: Proc Sql: Creating New Table with Format

talzy7 — Tue, 12 Feb 2019 13:28:27 GMT

 
 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.

Re: Proc Sql: Creating New Table with Format

RW9 — Tue, 12 Feb 2019 13:28:47 GMT

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>

Re: Proc Sql: Creating New Table with Format

talzy7 — Tue, 12 Feb 2019 13:32:16 GMT

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?

Re: Proc Sql: Creating New Table with Format

PaigeMiller — Tue, 12 Feb 2019 13:33:06 GMT

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.

Re: Proc Sql: Creating New Table with Format

talzy7 — Tue, 12 Feb 2019 13:34:01 GMT

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.

Re: Proc Sql: Creating New Table with Format

PaigeMiller — Tue, 12 Feb 2019 13:44:48 GMT

Show us

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

Re: Proc Sql: Creating New Table with Format

RW9 — Tue, 12 Feb 2019 13:45:28 GMT

"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.

Re: Proc Sql: Creating New Table with Format

talzy7 — Tue, 12 Feb 2019 13:49:22 GMT

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.

Re: Proc Sql: Creating New Table with Format

PaigeMiller — Tue, 12 Feb 2019 13:56:28 GMT

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.

Re: Proc Sql: Creating New Table with Format

talzy7 — Tue, 12 Feb 2019 14:03:54 GMT

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