topic Re: Applying format in SQL table in SAS Programming

Applying format in SQL table

Babloo — Fri, 13 Sep 2019 13:42:35 GMT

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.

Re: Applying format in SQL table

Jagadishkatam — Fri, 13 Sep 2019 14:07:47 GMT

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;

Re: Applying format in SQL table

Babloo — Fri, 13 Sep 2019 14:11:35 GMT

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

Re: Applying format in SQL table

ballardw — Fri, 13 Sep 2019 14:20:37 GMT

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

Re: Applying format in SQL table

Babloo — Fri, 13 Sep 2019 14:32:44 GMT

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

Re: Applying format in SQL table

Reeza — Fri, 13 Sep 2019 14:53:42 GMT

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.

Re: Applying format in SQL table

ballardw — Fri, 13 Sep 2019 17:46:34 GMT

@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;

Re: Applying format in SQL table

Babloo — Fri, 13 Sep 2019 18:45:44 GMT

Thank you, I was asked to use only data step

Re: Applying format in SQL table

Patrick — Mon, 16 Sep 2019 08:42:58 GMT

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