Data set and merge table refuse new column

[azertyuiop]

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

[RW9]

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;

[azertyuiop]

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;

[RW9]

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;

[azertyuiop]

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;

[Kurt_Bremser]

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.

[azertyuiop]

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. 

[Kurt_Bremser]

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

[azertyuiop]

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

[RW9]

Missing "then":

if a then way="abc";

[Kurt_Bremser]

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.

[Kurt_Bremser · Accepted solution]

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;

[azertyuiop]

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.