topic Re: Amend Formating in table in SAS Enterprise Guide

Amend Formating in table

zdassu — Tue, 05 Sep 2017 11:22:30 GMT

Hi your help in this matter would be much appreciated, I am using SAS Enterprise Guide 7.1 and I have taken over someones process. They previously used to import an external CSV file into SAS below is the code previously used. I have also taken a screenshot of the formats etc of the table it goes into

DATA recs.recs;

    LENGTH
        INV_NUMBER         8
		INV_BILLING_PERIOD_YEAR   8
        INV_BILLING_PERIOD_MONTH   8
		NMR_METER_POINT_REFERENCE   8
		RCH_RECONCILIATION_QTY   8
		LDZ_IDENTIFIER   $ 15
		RVE_START_DATE     8
		RVE_END_DATE       8
		NMR_START_METER_READ_DATE   8
        NMR_END_METER_READ_DATE   8;

	FORMAT
        INV_NUMBER       BEST32.
        INV_BILLING_PERIOD_YEAR BEST32.
        INV_BILLING_PERIOD_MONTH BEST32.
        NMR_METER_POINT_REFERENCE BEST32.
        RCH_RECONCILIATION_QTY BEST32.
        LDZ_IDENTIFIER   $CHAR15.
        RVE_START_DATE   YYMMDD10.
        RVE_END_DATE     YYMMDD10.
        NMR_START_METER_READ_DATE YYMMDD10.
        NMR_END_METER_READ_DATE YYMMDD10. ;

INFORMAT
        INV_NUMBER       BEST32.
        INV_BILLING_PERIOD_YEAR BEST32.
        INV_BILLING_PERIOD_MONTH BEST32.
        NMR_METER_POINT_REFERENCE BEST32.
        RCH_RECONCILIATION_QTY BEST32.
        LDZ_IDENTIFIER   $CHAR15.
        RVE_START_DATE   YYMMDD10.
        RVE_END_DATE     YYMMDD10.
        NMR_START_METER_READ_DATE YYMMDD10.
        NMR_END_METER_READ_DATE YYMMDD10. ;
    
 
Infile "Drop/LSPRec_201706.csv"
LRECL=1029
        TERMSTR=CRLF
        truncover
        firstobs=3
		obs=60000;
    INPUT
       
        @1 INV_NUMBER       : ?? BEST6.
		@41 INV_BILLING_PERIOD_YEAR : ?? BEST4.
        @81 INV_BILLING_PERIOD_MONTH : ?? BEST2.
		@121 NMR_METER_POINT_REFERENCE : ?? BEST10.
		@161 RCH_RECONCILIATION_QTY : ?? BEST8.
		@201 LDZ_IDENTIFIER   : $CHAR2.
		@216 RVE_START_DATE   : ?? YYMMDD10.
		@231 RVE_END_DATE     : ?? YYMMDD10.
		@244 NMR_START_METER_READ_DATE : ?? YYMMDD10.
        @270 NMR_END_METER_READ_DATE : ?? YYMMDD10.;
	
RUN;

/*check that you have imported in the same amount that is in the CSV file*/

proc append base=rich_dat.recs data=recs.recs;
run;

The Data set is much bigger now over 1.5 million rows and someone dumps the data into a a table, all the column names are the same but the Formats and lengths are different. I have added a screenshot below of the lengths and Formats. I would like the formats to be the same as the above. How do i go about changing the formats lengths etc?

Re: Amend Formating in table

zdassu — Tue, 05 Sep 2017 11:28:15 GMT

My Apologies I forgot to add the screenshot of the table with the data that needs formatting

Re: Amend Formating in table

TomKari — Tue, 05 Sep 2017 13:18:40 GMT

I'm not aware of a task-based method to change SAS dataset attributes in EG. However, there are two easy mechanisms to do so:

1. Create a new query using the query builder, and change the formats on the "Select Data" pane.

2. The "Datasets" procedure makes it very easy to change attributes. For example, I took a copy of "sashelp.shoes" into "work.shoes", and used this code to change some attributes:

proc datasets lib=work nolist;
modify shoes;
attrib sales format=best15.;
attrib inventory informat=best15.;
attrib returns label='new label';
quit;

Re: Amend Formating in table

zdassu — Tue, 05 Sep 2017 13:59:46 GMT

Hi how would i amend the data type from charachter to numeric, as the data type is currently charachter it does not let me use the format best

Re: Amend Formating in table

TomKari — Tue, 05 Sep 2017 14:38:01 GMT

Oh, now THAT's a different topic!

You can't change the data type of a variable "in place", which isn't surprising.

If you want to use a task, you can use the Query Builder, create a new advanced expression, and transform the variable using the "input" function. If you want to convert "problem_var", the expression

input(problem_var, best15.)

will almost certainly do the job, but make sure you do a good job of testing!

Tom

Re: Amend Formating in table

ballardw — Tue, 05 Sep 2017 16:11:03 GMT

@zdassu wrote:

Hi your help in this matter would be much appreciated, I am using SAS Enterprise Guide 7.1 and I have taken over someones process. They previously used to import an external CSV file into SAS below is the code previously used. I have also taken a screenshot of the formats etc of the table it goes into

The Data set is much bigger now over 1.5 million rows and someone dumps the data into a a table, all the column names are the same but the Formats and lengths are different. I have added a screenshot below of the lengths and Formats. I would like the formats to be the same as the above. How do i go about changing the formats lengths etc?

One thing to ask management would be if there is an agreement as to the file layout changing and is this new format "stable", as in not going to change frequently.

I will say that variables with names like Month or Year with 40 columns sounds very strange. In fact seeing 40 replicated so many places makes me think of some database default length and possibly type that wasn't considered very much. And with CSV I would look at the data file in a text editor (NOT a spreadsheet) to verify that the values are actually occupying close to 40 columns. If not, and especially if you see values like 1 to 12 in the Inv_Billing_Period_Month, I might strongly keep the current variable type,informat and format. I would check an example file for every one of those $40 fields.

The Rch_Reconciliation_Qty is another very suspicious character variable as QTY makes me believe this is a Quantity and if so really should be numeric.

And an aside, just because an INFORMAT changes doesn't mean that you have to change the FORMAT. I get dates in a number of formats and display them all in the display format I prefer (or organization policy sets).

Re: Amend Formating in table

Tom — Tue, 05 Sep 2017 19:42:36 GMT

Let me make sure I understand the question.

It sounds like that instead of running the data step you posted to create a dataset from a CSV file you are now being given a SAS dataset. And your question is how to transform that dataset to look like the data you used to read from the CSV file so that the rest of the code in the process doesn't need to change.

Re: Amend Formating in table

Tom — Tue, 05 Sep 2017 19:43:52 GMT

Pictures of the variable names and formats are nice, but not very useful for writing code. Can't you just copy and paste the output of PROC CONTENTS so that we have the variable names and formats as text instead of graphics?

Re: Amend Formating in table

zdassu — Wed, 06 Sep 2017 10:17:58 GMT

I have used the following code and it has actually completed what i wanted to achieve.

Thanks everyone for your help

PROC SQL;
   CREATE TABLE Work.Test AS 
   SELECT /* INV_NUMBER */
            (INPUT(t1.INV_NUMBER, best32.))format=best32. informat=best32. LENGTH=8 LABEL="INV_NUMBER" AS INV_NUMBER, 
          /* INV_BILLING_PERIOD_YEAR */
            (INPUT(t1.INV_BILLING_PERIOD_YEAR, best32.)) format=best32. informat=best32. LENGTH=8 LABEL="INV_BILLING_PERIOD_YEAR" AS 
            INV_BILLING_PERIOD_YEAR, 
          /* INV_BILLING_PERIOD_MONTH */
            (INPUT(t1.INV_BILLING_PERIOD_MONTH, best32.)) format=best32. informat=best32. LENGTH=8 LABEL="INV_BILLING_PERIOD_MONTH" AS 
            INV_BILLING_PERIOD_MONTH, 
          /* NMR_METER_POINT_REFERENCE */
            (INPUT(t1.NMR_METER_POINT_REFERENCE, best32.)) format=best32. informat=best32. LENGTH=8 LABEL="NMR_METER_POINT_REFERENCE" AS 
            NMR_METER_POINT_REFERENCE, 
          /* RCH_RECONCILIATION_QTY */
            (INPUT(t1.RCH_RECONCILIATION_QTY, Best32.)) format=best32. informat=best32. LENGTH=8 LABEL="RCH_RECONCILIATION_QTY" AS 
            RCH_RECONCILIATION_QTY, 
          /* LDZ_IDENTIFIER */
            (t1.LDZ_IDENTIFIER) FORMAT=$CHAR15. informat=$CHAR15. LENGTH=15 LABEL="LDZ_IDENTIFIER" AS LDZ_IDENTIFIER, 
          /* RVE_START_DATE */
            (t1.RVE_START_DATE) FORMAT=YYMMDD10. informat=YYMMDD10. LENGTH=8 LABEL="RVE_START_DATE" AS RVE_START_DATE, 
          /* RVE_END_DATE */
            (t1.RVE_END_DATE) FORMAT=YYMMDD10. informat=YYMMDD10. LENGTH=8 LABEL="RVE_END_DATE" AS RVE_END_DATE, 
          /* NMR_END_METER_READ_DATE */
            (t1.NMR_END_METER_READ_DATE) FORMAT=YYMMDD10. informat=YYMMDD10. LENGTH=8 LABEL="NMR_END_METER_READ_DATE" AS 
            NMR_END_METER_READ_DATE, 
          /* NMR_START_METER_READ_DATE */
            (t1.NMR_START_METER_READ_DATE) FORMAT=YYMMDD10. informat=YYMMDD10. LENGTH=8 LABEL="NMR_START_METER_READ_DATE" AS 
            NMR_START_METER_READ_DATE
      FROM Work.Test1;
QUIT;