Sas to Postgres Migration

November 3, 2022

I need to change sas code into PostgreSQL. But I couldn’t understand this code.
Please help me to translate following code into SQL code.

data fc_action_v1;      

     set action_targets; 

          Region_mod = upcase(region); 

          rename '2nd_Item_nr'n = Prodnumber;  

run;

Is this above sas code equivalent to following sql code?

create table data fc_action_v1 as
select 
     region_mod as region,
     2nd_item_nr as prodnumber
from action_targets

or above sas code mean this?
create table data fc_action_v1 as
select *,
region_mod as region,
2nd_item_nr as prodnumber
from action_targets

or something else? Please help me to translate above sas code into PostgreSQL SQL

>Solution :

In SQL you will need to type out ALL of the variables you want to copy over. (one reason to continue to use SAS code instead).

Your SQL is not making a NEW variable named REGION_MOD. And it is not setting it to the upper case version of what is in REGION.

I suspect that POSTGRESQL will have the same problem with the variable name that starts with a digit as SAS did, so use whatever syntax POSTGRESQL supports to referencing non-standard variable names. ANSI standards should support using a quoted name. (But watch out for the case you use in the name as PostgreSQL has strange rules for the case of variable names).

So something more like:

create table fc_action_v1 as
select var1
     , region
     , var2 
     , "2nd_item_nr" as prodnumber
     , var3
     , upper(region) as region_mod
from action_targets

Where var1,var2,and var3 are just to illustrate that you need to list ALL of the variables (in the order you want them to appear in the dataset). The SAS datastep will add the new variable REGION_MOD as the last one in the dataset.