Applying format in SQL table

[Babloo]

I'm unable to copy the user defined Format of one SAS variable to the SQL table. In the below example, 'metadata_final' is a SAS dataset where it holds the formatted value for the variable STAT_CD. However when I tried copy the SAS table which has that formatted variable in SQL table it is not copying the formatted values instead it is copying the actual value.

Log is given below and even if I apply the Format Statement before creating the SQL table, it is not taking the Format. No WARNING or ERROR message in the log as well.

May I know how can I copy the formatted value in SQL table?

27         data SQLLIB.SQL_table;
28         Format STAT_CD $STAT_CD.;
29         	set metadata_final;
30         	run_id=sum(n,1);
31         	output;
32         	stop;
33         	set SQLLIB.SQL_table nobs=n;
34         	modify SQLLIB.SQL_table;
35         run;

NOTE: There were 1 observations read from the data set WORK.METADATA_FINAL.
NOTE: The data set SQLLIB..SQL_TABLE has been updated.  There were 0 observations rewritten, 1 observations added and 0 observations 
      deleted.

[Jagadishkatam]

please try below code

   data SQLLIB.SQL_table;
   	set metadata_final;
   	run_id=sum(n,1);
   	output;
   	stop;
   	set SQLLIB.SQL_table nobs=n;
   	modify SQLLIB.SQL_table;
     Format STAT_CD $STAT_CD.;
   run;

[Babloo]

No, it's not working. I already tried placing the FORMAT Statement at different positions and it's not working.

[ballardw]

---

@Babloo wrote:

I'm unable to copy the user defined Format of one SAS variable to the SQL table. In the below example, 'metadata_final' is a SAS dataset where it holds the formatted value for the variable STAT_CD. However when I tried copy the SAS table which has that formatted variable in SQL table it is not copying the formatted values instead it is copying the actual value.

---

That the actual value is ALWAYS copied is the way SAS works. Always. If you want the "formatted value" as the actual value then you need to use something explict like Put(variable,formatname.) as FormattedValue (SQL) or FormattedValue = put(variable,formatname.) (data step) At which point the formatted value is not the actual value and often not the same type (numeric to character conversion with PUT) or length.

[Babloo]

Got it. Can we do this without creating the new variable while I do with
PUT function?

[Reeza]

Not really, either way you're creating a new variable. You could create a view that did the conversion and then upload the view to avoid changing names if that's your issue.

[ballardw]

---

@Babloo wrote:
Got it. Can we do this without creating the new variable while I do with
PUT function?

---

It would depend to some extent on the existing variable type and if using a data step the existing defined length.

In a data step changing types would fail.

SQL is apparently less concerned so you can use the same variable name:

proc format library=work;
value $someformat
"This is a test" ="Only a test"
;
data example;
   x = "This is a test";
   y = 12345;
run;

proc sql; 
   create table work.outformat as
   select Put(x,$someformat.) as x, put(y,comma9.) as y
   from example
   ;
quit;

[Babloo]

Thank you, I was asked to use only data step

[Patrick · Accepted solution]

---

@Babloo wrote:
Thank you, I was asked to use only data step

---

That's a strange requirement. But o.k! Given your initial post something like below could work. This assumes that only reading the first row from your source table like in the code you've posted is what you need.

options sastrace='d' sastraceloc=saslog nostsuffix;
data SQLLIB.SQL_table;
	set metadata_final(rename=(STAT_CD=_STAT_CD));
	run_id=sum(n,1);
  STAT_CD=put(_STAT_CD,$STAT_CD.);
  drop _STAT_CD;
	output;
	stop;
run;

If it was me then I'd split the process into first preparing the data in SAS and then use Proc Append for the Insert into SQL Server.

options sastrace='d' sastraceloc=saslog nostsuffix;
data prep(drop=_stat_cd);
	set metadata_final(obs=1 rename=(STAT_CD=_STAT_CD));
	run_id=sum(n,1);
  STAT_CD=put(_STAT_CD,$STAT_CD.);
run;

proc append base=SQLLIB.SQL_table data=prep;
run;

Alternatively:

- Load the data "as is" into SQL (=unformatted values)

- Load the SAS format as key/value pairs into another SQL table (you can easily get such a table by using Proc Format with CNTLOUT)

- Create a view in Oracle which left-joins with the table with key/value pairs to create the column with formatted values

The advantage of such an approach: If you ever have to amend the format on the SAS side then you have only to re-load the key/value table in SQL but you don't need to come-up with code which changes the values in your main table (something you would need to do if loading the formatted values only).