Sas to Postgres Migration

Question

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

Your SQL code is ignoring all of the other variables that might exist in the input dataset ACTION_TARGETS. In SQL you will probably have to type out ALL of the variable names. — Tom
– Tom, Commented Nov 3, 2022 at 17:30
I have ignored uppercase, other than that I couldn't understand other sad code. Please sql code so i can understand — Awesome
– Awesome, Commented Nov 3, 2022 at 17:38

Tom · Accepted Answer · 2022-11-03 17:37:44Z

1

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.

answered Nov 3, 2022 at 17:37

Tom

52.3k2 gold badges19 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Awesome Over a year ago

if that table has 1000+ columns then should I use *,region,"2nd_item_nr" as prodnumber, upper(region) as region_mod instead? but * and other column will make those other column repeat again

Tom Over a year ago

You could use *, but then you would need to add a step to drop the variable "2nd_itme_nr" as it will be included in the list of variables that * implies. And if the order of the variables in the table matter to you the new version of it will be at the end instead of in the place where it was when it had the old name. Another difference might be if there was already a variable name REGION_MOD in the input dataset. You cannot create two variables with the same name.

Collectives™ on Stack Overflow

Sas to Postgres Migration

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related