topic Re: Problem with merging two tables in SAS Programming

Problem with merging two tables

Num19 — Mon, 08 Jan 2018 11:20:59 GMT

Hi, I would like to merge two tables (1-n) but I can't, I have this kind of result :

Here is my code :

DATA tabTemp;
SET malib.JO_organisation_annee (DROP=date_debut--nb_athletes_f);
RUN;

PROC SORT DATA=tabTemp OUT=work.Jo_organisation_m;
BY annee;
RUN;

PROC SORT DATA=malib.Jo_medailles OUT=work.Jo_medailles_m;
BY annee;
RUN;

DATA TEMP;
MERGE Jo_organisation_m (IN=A) Jo_medailles_m (IN=B);
By annee;
RUN;

Thanks for your help.

Re: Problem with merging two tables

PeterClemmensen — Mon, 08 Jan 2018 11:22:47 GMT

Post some sample data along with your problem we can use.

It makes it much easier to help 🙂

Re: Problem with merging two tables

Num19 — Mon, 08 Jan 2018 11:37:20 GMT

Sorry, there is two tables, the first is JO_organisation_annee, the second is created by this code :

DATA malib.Jo_medailles;
INFILE "C:\Users\Desktop\Projet\Résultats Jeux Olympiques.txt" FIRSTOBS=10;
RETAIN code_discipline discipline;
INPUT test :$32. @;
	  IF test="Discipline" THEN INPUT test $ code_discipline $ discipline :$CHAR32.;
	  	  ELSE IF test in(1924:2014) THEN DO;
			INPUT annee $ 1-12 code_pays $ 14-16 pays $ 25-48 type_medaille $ 49-63 nb_medailles 64;
			OUTPUT;
	  END; 
DROP test;
RUN;

Thank you

Re: Problem with merging two tables

RW9 — Mon, 08 Jan 2018 11:40:48 GMT

Posting pictures doesn't really help much. From that picture however, it can clearly be seen that in the first table the variable annee contains a number for instance ' 1924' - possibly meaning a year. In the second table annee seems to contain text data 'AUS' for example. As these clearly are not the same, then the data from the second table appears under the first, and no merge occurs.

Without some further information I can't say anything else other than avoid coding all in uppercase or mixed case, it makes reading the code so much harder:

data tabtemp;
  set malib.jo_organisation_annee (drop=date_debut--nb_athletes_f);
run;

Re: Problem with merging two tables

Num19 — Mon, 08 Jan 2018 11:45:15 GMT

Annee are both numeric values in those two tables.

How can I add a table (attachments) ?

Re: Problem with merging two tables

RW9 — Mon, 08 Jan 2018 12:06:59 GMT

Your import code is not working correctly. After some fiddling with it I got to this, you can take it further from here and process out to what you want:

data jo_medailles;
  infile "s:temp/r.txt" firstobs=10 dlm='*';
  length lne year country country_long $200;
  retain lne;
  input;
  if substr(strip(_infile_),1,5)="Disci" then lne=_infile_;
  else if substr(_infile_,1,5)="-----" then delete;
  else do;
    year=strip(substr(_infile_,1,4));
    country=strip(substr(_infile_,6,11));
    country_long=strip(substr(_infile_,17,30));
    output;
  end;
run;

Also, that file pretty bad for importing, is there not a way you could get a nice CSV or XML version of the data, would make your life easier than some report file.

Re: Problem with merging two tables

Kurt_Bremser — Mon, 08 Jan 2018 12:10:38 GMT

Post example data in data steps. Use the macro from https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-data-AKA-generate/ta-p/258712 to convert your dataset(s) to data step(s), and then post the resulting code in a code window as described in https://communities.sas.com/t5/help/faqpage/faq-category-id/posting?nobounce.

Use the same method to post your code, as the code subwindow will preserve formatting (indentation) and not change certain specific characters/character sequences.

Re: Problem with merging two tables

Kurt_Bremser — Mon, 08 Jan 2018 12:12:30 GMT

@Num19 wrote:

Annee are both numeric values in those two tables.

Can't be. 'AUS' is not a numeric value, so annee must be of type character. Or you have a numeric value behind 'AUS' that is displayed as 'AUS' because of a format. But then it's clearly not a year.

Re: Problem with merging two tables

Kurt_Bremser — Mon, 08 Jan 2018 12:14:14 GMT

Seeing this code piece:

INPUT annee $ 1-12

it becomes clear that annee is NOT numeric.

Re: Problem with merging two tables

Num19 — Mon, 08 Jan 2018 13:03:56 GMT

I don't know where is the Autoexec file.

Sorry but I meant Alphanumeric.

Here is the good pic :

I'm sorry but I can't use an other way to import data I have to follow some instructions.

Via that code :

DATA import;
INFILE CARDS4 DLM=";";
RETAIN nom; 
INPUT test $ @; 
	  IF test ="NOM" THEN INPUT nom $;
	  ELSE IF test="PRENOM" THEN DO; 
			INPUT prenom $;
			OUTPUT;
	  END;
DROP test; 
CARDS4; 
NOM;DUPONT
PRENOM;PIERRE
PRENOM;SOPHIE
NOM;MARTIN
PRENOM;THOMAS
;;;; 
RUN;

I had to build one of my own to import data from (Inspiring of the previous code) :

"C:\Users\Desktop\Projet\Résultats Jeux Olympiques.txt"

And that's what I did :

DATA malib.Jo_medailles;
INFILE "C:\Users\Réda\Desktop\Organisation\Master\Maths et Finance\S1\SAS\Tables\Résultats Jeux Olympiques.txt" FIRSTOBS=10;
RETAIN code_discipline discipline;
INPUT test :$32. @;
	  IF test="Discipline" THEN INPUT test $ code_discipline $ discipline :$CHAR32.;
	  	  ELSE IF test in(1924:2014) THEN DO;
			INPUT annee $ 1-12 code_pays $ 14-16 pays $ 25-48 type_medaille $ 49-63 nb_medailles 64;
			OUTPUT;
	  END; 
DROP test;
RUN;

Re: Problem with merging two tables

Kurt_Bremser — Mon, 08 Jan 2018 13:02:37 GMT

So you have differently formatted values (right- or left-aligned) in annee. Before merging, you need to standardize your data; since all the values are indeed numeric, I'd convert to a numeric variable in both datasets, using this code:

data jo_organisation_m;
set malib.jo_organisation_annee (drop=date_debut--nb_athletes_f);
annee_num = input(annee,best.);
drop annee;
run;

proc sort data=jo_organisation_m;
by annee_num;
run;

data jo_medailles_m;
set malib.jo_medailles;
annee_num = input(annee,best.);
drop annee;
run;

proc sort data=jo_medailles_m;
by annee_num;
run;

data temp;
merge
  jo_organisation_m (in=A)
  jo_medailles_m (in=B)
;
by annee_num;
run;

Re: Problem with merging two tables

Num19 — Mon, 08 Jan 2018 13:19:13 GMT

Out of the topic but why do I have in "Discipline" the data Ski instead of "Ski de fond" or "Ski acrobatique" like in the text file ?

Re: Problem with merging two tables

Kurt_Bremser — Mon, 08 Jan 2018 14:23:26 GMT

@Num19 wrote:

Out of the topic but why do I have in "Discipline" the data Ski instead of "Ski de fond" or "Ski acrobatique" like in the text file ?

Because you use simple list input in the respective input statement, and that means that a blank will be used as a separator.

That particular piece of code would better look like this:

data malib.jo_medailles;
infile
  "C:\Users\Réda\Desktop\Organisation\Master\Maths et Finance\S1\SAS\Tables\Résultats Jeux Olympiques.txt"
  firstobs=10
  truncover
;
retain code_discipline discipline;
input test :$10. @;
if test = "Discipline"
then input test $ code_discipline $ discipline $char32.;
else if test ne '----------'
then do;
  input
    annee $ 1-12
    code_pays $ 14-16
    pays $ 25-48
    type_medaille $ 49-63
    nb_medailles 64
  ;
  output;
end; 
drop test;
run;