Desktop productivity for business analysts and programmers

Data set and merge table refuse new column

Accepted Solution Solved
Reply
Contributor
Posts: 68
Accepted Solution

Data set and merge table refuse new column

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


Accepted Solutions
Solution
‎11-10-2017 07:21 AM
Super User
Posts: 10,535

Re: Data set and merge table refuse new column

Posted in reply to azertyuiop

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;

 

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code

View solution in original post


All Replies
Super User
Super User
Posts: 9,813

Re: Data set and merge table refuse new column

Posted in reply to azertyuiop

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;
Contributor
Posts: 68

Re: Data set and merge table refuse new column

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;
Super User
Super User
Posts: 9,813

Re: Data set and merge table refuse new column

Posted in reply to azertyuiop

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 @KurtBremser 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;
Contributor
Posts: 68

Re: Data set and merge table refuse new column

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;

 

Super User
Posts: 10,535

Re: Data set and merge table refuse new column

Posted in reply to azertyuiop

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.
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Contributor
Posts: 68

Re: Data set and merge table refuse new column

Posted in reply to KurtBremser

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. 

Super User
Posts: 10,535

Re: Data set and merge table refuse new column

Posted in reply to azertyuiop

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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Contributor
Posts: 68

Re: Data set and merge table refuse new column

Posted in reply to KurtBremser

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

Super User
Super User
Posts: 9,813

Re: Data set and merge table refuse new column

Posted in reply to azertyuiop

Missing "then":

if a then way="abc";
Super User
Posts: 10,535

Re: Data set and merge table refuse new column

Posted in reply to azertyuiop

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Solution
‎11-10-2017 07:21 AM
Super User
Posts: 10,535

Re: Data set and merge table refuse new column

Posted in reply to azertyuiop

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;

 

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Contributor
Posts: 68

Re: Data set and merge table refuse new column

[ Edited ]
Posted in reply to KurtBremser

Hello ,

 

@

 

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

 

It's a excellent new Man Happy

 

@RW9

 

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

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 12 replies
  • 320 views
  • 4 likes
  • 3 in conversation