topic Re: Data set and merge table refuse new column in SAS Enterprise Guide

Data set and merge table refuse new column

azertyuiop — Fri, 10 Nov 2017 11:09:59 GMT

Hello ,

I must merge 4 tables in SAS without using proc sql and union. I must usge DATA and SET.

data quatre_fichiers;
 
set work.bd1_imp 

/*additional column*/ way='transac_way1'
(rename=("N°"n=num
Application=app  
"Nature transac"n=lbnat 
"Cause transac"n=lbcau
"Anomalie(s)"n=anomalie  
"Date rejet "n=dtrej 
Acteur=lbact 
Statut=lbsta 
"Analyse srv1"n=anasrv1  
"Analyse srv2"n=ana 
"Date correction"n=dtcorr
initiale=trigramme));

set work.bd2_imp 

/*additional column*/ way='transac_way2'
(rename=("N°"n=num
Application=app  
"Nature transac"n=lbnat 
"Cause transac"n=lbcau
"Anomalie(s)"n=anomalie  
"Date rejet "n=dtrej 
Acteur=lbact 
Statut=lbsta 
"Analyse srv1"n=anasrv1  
"Analyse srv2"n=ana 
"Date correction"n=dtcorr
initiale=trigramme));

set work.bd3_imp 

/*additional column*/ way='transac_way3'
(rename=("N°"n=num
Application=app  
"Nature transac"n=lbnat 
"Cause transac"n=lbcau
"Anomalie(s)"n=anomalie  
"Date rejet "n=dtrej 
Acteur=lbact 
Statut=lbsta 
"Analyse srv1"n=anasrv1  
"Analyse srv2"n=ana 
"Date correction"n=dtcorr
initiale=trigramme));

set work.bd4_imp 

/*additional column*/ way='transac_way4'
(rename=("N°"n=num
Application=app  
"Nature transac"n=lbnat 
"Cause transac"n=lbcau
"Anomalie(s)"n=anomalie  
"Date rejet "n=dtrej 
Acteur=lbact 
Statut=lbsta 
"Analyse srv1"n=anasrv1  
"Analyse srv2"n=ana 
"Date correction"n=dtcorr
initiale=trigramme));
 
run;

Sas refuse to insert the new column in first position in my new table (this new column is " way " ). This column must contain a special value in function of the table which is insert . If the data come from the table work.bd1, I want see 'transac_way1' in the new column which is own the name ' way ' , if it's work.bd2 I want 'transac_way2' .

In this case SAS refuse to read the instruction way='value' . It's the event case if I place instruction attrib or label .

Thanks for your help

Re: Data set and merge table refuse new column

RW9 — Fri, 10 Nov 2017 11:24:39 GMT

Sorry, your question isn't clear. If you just want the variable way to appear as the first variable in the dataset, then you need to initialise it before reading the other data, something simple like:

data want;
  length way $100;
  set...;
run;

Re: Data set and merge table refuse new column

azertyuiop — Fri, 10 Nov 2017 11:40:56 GMT

Hello ,

When I add your instruction it's always the same problem . Sas Doesn't want add the column with the value ...

data quatre_fichiers;

length way $100;

set work.bd1_imp

/*additional column*/ way='transac_way1'
(rename=("N°"n=num
Application=app  
"Nature transac"n=lbnat
"Cause transac"n=lbcau
"Anomalie(s)"n=anomalie  
"Date rejet "n=dtrej
Acteur=lbact
Statut=lbsta
"Analyse srv1"n=anasrv1  
"Analyse srv2"n=ana
"Date correction"n=dtcorr
initiale=trigramme));

set work.bd2_imp

/*additional column*/ way='transac_way2'
(rename=("N°"n=num
Application=app  
"Nature transac"n=lbnat
"Cause transac"n=lbcau
"Anomalie(s)"n=anomalie  
"Date rejet "n=dtrej
Acteur=lbact
Statut=lbsta
"Analyse srv1"n=anasrv1  
"Analyse srv2"n=ana
"Date correction"n=dtcorr
initiale=trigramme));

set work.bd3_imp

/*additional column*/ way='transac_way3'
(rename=("N°"n=num
Application=app  
"Nature transac"n=lbnat
"Cause transac"n=lbcau
"Anomalie(s)"n=anomalie  
"Date rejet "n=dtrej
Acteur=lbact
Statut=lbsta
"Analyse srv1"n=anasrv1  
"Analyse srv2"n=ana
"Date correction"n=dtcorr
initiale=trigramme));

set work.bd4_imp

/*additional column*/ way='transac_way4'
(rename=("N°"n=num
Application=app  
"Nature transac"n=lbnat
"Cause transac"n=lbcau
"Anomalie(s)"n=anomalie  
"Date rejet "n=dtrej
Acteur=lbact
Statut=lbsta
"Analyse srv1"n=anasrv1  
"Analyse srv2"n=ana
"Date correction"n=dtcorr
initiale=trigramme));
 
run;

Re: Data set and merge table refuse new column

Kurt_Bremser — Fri, 10 Nov 2017 12:07:33 GMT

So you have 4 tables of identical structure, that need to be concatenated (merge doesn't make sense, you would lose three quarters of the data because of identical variable names).

And you want to have a flag that holds a pointer to where the data came from.

Do this:

data quatre_fichiers;
length way $32; /* puts way in first position */
set
  work.bd1_imp
  work.bd2_imp
  work.bd3_imp
  work.bd4_imp
  indsname=inds
;
rename
  "N°"n=num
  Application=app  
  "Nature transac"n=lbnat 
  "Cause transac"n=lbcau
  "Anomalie(s)"n=anomalie  
  "Date rejet "n=dtrej 
  Acteur=lbact 
  Statut=lbsta 
  "Analyse srv1"n=anasrv1  
  "Analyse srv2"n=ana 
  "Date correction"n=dtcorr
  initiale=trigramme
;
way = inds;
run;

Re: Data set and merge table refuse new column

RW9 — Fri, 10 Nov 2017 12:18:45 GMT

You get errors as your code isn't valid:

set work.bd1_imp

/*additional column*/ way='transac_way1'

You do not assign data to values in a set statment.

Anyways, it appears @Kurt_Bremser has given you code which is far more succinct, so use that, although I would make a slight change so that it matches your way= text:

data quatre_fichiers;
length way $32; /* puts way in first position */
set
  work.bd1_imp (in=a)
  work.bd2_imp (in=b)
  work.bd3_imp (in=c)
  work.bd4_imp (in=c)
;
rename
  "N°"n=num
  Application=app  
  "Nature transac"n=lbnat 
  "Cause transac"n=lbcau
  "Anomalie(s)"n=anomalie  
  "Date rejet "n=dtrej 
  Acteur=lbact 
  Statut=lbsta 
  "Analyse srv1"n=anasrv1  
  "Analyse srv2"n=ana 
  "Date correction"n=dtcorr
  initiale=trigramme
;
if a way="transac_way1";
if b way="transac_way2";
if c way="transac_way3";
if d way="transac_way4";
run;

Re: Data set and merge table refuse new column

azertyuiop — Fri, 10 Nov 2017 12:25:59 GMT

Hello ,

@ KurtBremser

I confirm , I can now see the name and source of table in the column "way".

It's a excellent new

@RW9

I confirm , your solution too . It's an other system for me if I must use a specifical value.

Re: Data set and merge table refuse new column

azertyuiop — Fri, 10 Nov 2017 12:40:31 GMT

When I process this instruction " if d way="transac_way4"; "

SAS refuse to execute the code and can't affect the value in " way " field .

There is this message

ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, (, *, **, +, -, /, ;, <, <=, <>, =, >, ><, >=, AND, EQ, GE, 
              GT, IN, LE, LT, MAX, MIN, NE, NG, NL, NOTIN, OR, [, ^=, {, |, ||, ~=.

data quatre_fichiers;
length way $32; /* puts way in first position */
set
  work.bd1_imp (in=a)
  work.bd2_imp (in=b)
  work.bd3_imp (in=c)
  work.bd4_imp (in=c)
;
rename
  "N°"n=num
  Application=app  
  "Nature transac"n=lbnat 
  "Cause transac"n=lbcau
  "Anomalie(s)"n=anomalie  
  "Date rejet "n=dtrej 
  Acteur=lbact 
  Statut=lbsta 
  "Analyse srv1"n=anasrv1  
  "Analyse srv2"n=ana 
  "Date correction"n=dtcorr
  initiale=trigramme
;
if a way="transac_way1";
if b way="transac_way2";
if c way="transac_way3";
if d way="transac_way4";
run;

Re: Data set and merge table refuse new column

Kurt_Bremser — Fri, 10 Nov 2017 12:43:03 GMT

That's because of a typo in

work.bd4_imp (in=c)

it should be

work.bd4_imp (in=d)

Look at the log. You'll find a message

NOTE: variable d is unitialized.

Re: Data set and merge table refuse new column

Kurt_Bremser — Fri, 10 Nov 2017 12:49:05 GMT

Now, if you would use in1 to in4 for your in= variables (instead of a to d), you now get a structure that lends itself very well to automating repeated code with a macro, so that you could combine an arbitrary set of similarly named datasets by specifiying just one number.

Re: Data set and merge table refuse new column

azertyuiop — Fri, 10 Nov 2017 12:50:26 GMT

I have correct the C by a letter D .

But the problem appear here :

if a way="transac_way1";
if b way="transac_way2";
if c way="transac_way3";
if d way="transac_way4";

The word "way" for each line here is considered like an error by SAS. SAS say that an operator symbol is missing or that it's necessary to use an other.

Re: Data set and merge table refuse new column

Kurt_Bremser — Fri, 10 Nov 2017 12:56:13 GMT

Look up the documentation of the IF data step statement, and you will immediately be able to fix @RW9's mistake.

Re: Data set and merge table refuse new column

RW9 — Fri, 10 Nov 2017 13:07:44 GMT

Missing "then":

if a then way="abc";

Re: Data set and merge table refuse new column

azertyuiop — Fri, 10 Nov 2017 13:07:46 GMT

I have found the correction.

Element "then" in the instruction is missing. I have add this element .

http://support.sas.com/documentation/cdl/en/lrdict/64316/HTML/default/viewer.htm#a000202239.htm