topic Re: Conversion of SQL pivot to proc sql in SAS Programming

Conversion of SQL pivot to proc sql

David_Billa — Mon, 26 Sep 2022 14:23:30 GMT

Any help to convert the following SQL DB query to SAS proc sql? Libname is already available to connect to SQL server.

SELECT * FROM (
                SELECT [MATRL_NBR]
                                  ,[CLAS]
                                  ,[CHRSTC_NME]
                                  ,[CHRSTC_VALUE]
                  FROM [ALLIAN].[KPA].[MATRL_CLASFCTN] 
) as t1
                PIVOT (
                                max([CHRSTC_VALUE])
                                FOR [CHRSTC_NME] in ([IP_BU], [IP_BRAND]) 
) as t2

Re: Conversion of SQL pivot to proc sql

yabwon — Mon, 26 Sep 2022 16:37:18 GMT

Could you share some example data? Both before and after SQL.

Bart

Re: Conversion of SQL pivot to proc sql

David_Billa — Mon, 26 Sep 2022 16:39:31 GMT

Sure, I'd like to know how to convert that SQL with Pivot in SAS.

Re: Conversion of SQL pivot to proc sql

PaigeMiller — Mon, 26 Sep 2022 16:41:33 GMT

PROC REPORT is one way to get a "pivot table" in SAS, but that depends on exactly what is meant by "pivot table", and so yes, definitely seeing (a portion of) the data and the desired output would be extremely helpful.

Re: Conversion of SQL pivot to proc sql

Reeza — Mon, 26 Sep 2022 17:17:01 GMT

I'd do this in two steps personally, fully dynamic of course.

proc sql;
create table t1 as
select matrl_nbr, clas, chrstc_nme, max(chrstc_value) as chrstc_value 
from kpa.matrl_clasfctn /*this may vary depending on how it's set up*/
where chrstc_nme in ('IP_BU', 'IP_BRAND');
group by matrl_nbr, clas, chrstc_nme;
quit;

proc transpose data=t1 out=t2;
by matrl_nbr clas chrstc_nme;
id chrstc_nme;
var chrstc_value;
run;

There's possibly a way to do this in one using proc summary and ID statement but @Tom is better at that type of code than I am.

How exactly you refer to the table name depends a bit on your set up to the library but you kpa should be the libname reference and the table name will be the same. Schema and DB are not relevant in this context, as they're specified in the libname definition.

Re: Conversion of SQL pivot to proc sql

Tom — Mon, 26 Sep 2022 17:20:21 GMT

Describe it words what you are trying to accomplish.

Explain what this PIVOT keyword does.

Share input and desired output datasets.

As SAS data steps to create the dataset(s) so we have something to test code with.

Re: Conversion of SQL pivot to proc sql

SASKiwi — Mon, 26 Sep 2022 19:27:21 GMT

Why do you need to convert it? You can run it unchanged in SAS SQL Passthru.

libname sqlsrvr odbc noprompt = "server=MyServerName;DRIVER=SQL Server;Trusted Connection=yes;";

proc sql;
 connect using sqlsrvr;
  create table Want  as 
  select * from connection to sqlsrvr
  (SELECT * FROM (
                SELECT [MATRL_NBR]
                                  ,[CLAS]
                                  ,[CHRSTC_NME]
                                  ,[CHRSTC_VALUE]
                  FROM [ALLIAN].[KPA].[MATRL_CLASFCTN] 
    ) as t1
                PIVOT (
                                max([CHRSTC_VALUE])
                                FOR [CHRSTC_NME] in ([IP_BU], [IP_BRAND]) 
    ) as t2
   )
  ;
quit;

Re: Conversion of SQL pivot to proc sql

David_Billa — Tue, 27 Sep 2022 07:41:32 GMT

@Tom @yabwon Hopefully you can understand now if you read my question from the post and then you see this data.

Input table:

MATRL_NBR	CLAS	CHRSTC_NM	CHRSTC_VAL
82011101811	SALES_DATA	IP_BRAND	XGIRL SCOUT AWARD
7500547966	SALES_DATA	IP_BRAND	NOT ASSIGN
82011101811	SALES_DATA	IP_BU	X COOKIES
7500547966	SALES_DATA	IP_BU	NA
K1634890000	PACKAGING	FOOD_FORM	WAFFLES
K1634890000	PACKAGING	SPECIAL_ID	24CT
82011101811	SALES_DATA	FOOD_FORM	WAFFLES
7500547966	SALES_DATA	SPECIAL_ID	24CT

Excepted Result:

MATRL_NBR	CLAS	IP_BU	IP_BRAND
82011101811	SALES_DATA	X COOKIES	XGIRL SCOUT AWARD
7500547966	SALES_DATA	NA	NOT ASSIGN
K1634890000	PACKAGING	NULL	NULL

Re: Conversion of SQL pivot to proc sql

Tom — Tue, 27 Sep 2022 12:07:49 GMT

I don't see any description of what it is you are trying to do.

Looks like you created new variables where the name is based on the values of one of the variables with the actual content is based on a second variable. You grouped the data by two of the variables.

SO that is what PROC TRANSPOSE does. Assuming that the MAX() aggregate function mentioned in the "pivot" code is just an artifact of how that tool works. If you actually have multiple IP_BRAND values in the same group then you need to explain why taking the maximum value makes any sense for your problem.

data have ;
  infile cards dsd truncover;
  input (MATRL_NBR CLAS CHRSTC_NM CHRSTC_VAL) (:$30.);
cards;
82011101811,SALES_DATA,IP_BRAND,XGIRL SCOUT AWARD
7500547966,SALES_DATA,IP_BRAND,NOT ASSIGN
82011101811,SALES_DATA,IP_BU,X COOKIES
7500547966,SALES_DATA,IP_BU,NA
K1634890000,PACKAGING,FOOD_FORM,WAFFLES
K1634890000,PACKAGING,SPECIAL_ID,24CT
82011101811,SALES_DATA,FOOD_FORM,WAFFLES
7500547966,SALES_DATA,SPECIAL_ID,24CT
;

proc sort data=have;
  by MATRL_NBR CLAS ;
run;

proc transpose data=have out=want;
  where CHRSTC_NM =: 'IP_' ;
  by MATRL_NBR CLAS ;
  id CHRSTC_NM ;
  var CHRSTC_VAL ;
run;

Result:

Obs     MATRL_NBR        CLAS         _NAME_      IP_BRAND             IP_BU

 1     7500547966     SALES_DATA    CHRSTC_VAL    NOT ASSIGN           NA
 2     82011101811    SALES_DATA    CHRSTC_VAL    XGIRL SCOUT AWARD    X COOKIES

Re: Conversion of SQL pivot to proc sql

Reeza — Tue, 27 Sep 2022 19:52:20 GMT

How does @Tom's solution incorporate the MAX value portion of the query? Or will it always be only one value?

Re: Conversion of SQL pivot to proc sql

Tom — Tue, 27 Sep 2022 19:53:41 GMT

@Reeza wrote:
How does @Tom's solution incorporate the MAX value portion of the query? Or will it always be only one value?

Which is why I asked whether the MAX() was actually part of the request or just an artifact of how the PIVOT syntax works. I might be that for PIVOT to work you are required to use some type of aggregate function.