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
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;
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;
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;
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;
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;
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.
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.
Look up the documentation of the IF data step statement, and you will immediately be able to fix @RW9's mistake.
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
Missing "then":
if a then way="abc";
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.
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;
Hello ,
I confirm , I can now see the name and source of table in the column "way".
It's a excellent new
I confirm , your solution too . It's an other system for me if I must use a specifical value.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.