topic sql transform equivalent in sas in SAS Programming

sql transform equivalent in sas

TB10 — Thu, 24 Aug 2023 05:31:48 GMT

I have the following type of code in sql:

TRANSFORM Max(ConfigValue)
SELECT Config_ID
FROM TABLE_01
GROUP BY Config_ID
PIVOT ConfigField

how do i convert it in sas to get the same result ?

@PaigeMiller @Quentin @fja @Tom

Re: sql transform equivalent in sas

yabwon — Thu, 24 Aug 2023 05:43:40 GMT

if it is the same case as from this stackoverflow thread:

https://stackoverflow.com/questions/16691853/transform-and-pivot-in-access-2013-sql

then use proc Transpose (https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/proc/n1xno5xgs39b70n0zydov0owajj8.htm):

data TABLE_01;
  infile cards dlm="|";
  input Config_ID  (ConfigField ConfigValue) (:$20.) ;
cards;
11|         Name  |        Basic
11|      Version  |         1.01
11|        Owner  |         Jack
12|         Name  |     Advanced
12|      Version  |         1.03
12|        Owner  |         Andy
;
run;
proc print;
run;


proc transpose data=TABLE_01 out=TABLE_02(drop=_NAME_);
  by Config_ID;
  id ConfigField;
  var ConfigValue;
run;
proc print;
run;

Bart

Re: sql transform equivalent in sas

Tom — Thu, 24 Aug 2023 13:24:42 GMT

@TB10 wrote:

I have the following type of code in sql:
TRANSFORM Max(ConfigValue)
SELECT Config_ID
FROM TABLE_01
GROUP BY Config_ID
PIVOT ConfigField 
how do i convert it in sas to get the same result ?

What does that gibberish code do? Show an example input. Describe the change you want to make. Show the resulting output for that input.

If you want to calculate some type of maximum value then I you might want to start with PROC SUMMARY.