- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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?
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
My Apologies I forgot to add the screenshot of the table with the data that needs formatting
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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?
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
@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).
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;