BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
zdassu
Quartz | Level 8

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

 

RecProperties.PNG

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?

 

1 ACCEPTED SOLUTION

Accepted Solutions
zdassu
Quartz | Level 8

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;

View solution in original post

8 REPLIES 8
zdassu
Quartz | Level 8

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

IncorrectTableProperties.PNG

TomKari
Onyx | Level 15

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;

zdassu
Quartz | Level 8

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

TomKari
Onyx | Level 15

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

Tom
Super User Tom
Super User

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?

ballardw
Super User

@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).

 

 

Tom
Super User Tom
Super User

 

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.

zdassu
Quartz | Level 8

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 1343 views
  • 0 likes
  • 4 in conversation