BookmarkSubscribeRSS Feed
Num19
Calcite | Level 5

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.

12 REPLIES 12
PeterClemmensen
Tourmaline | Level 20

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

 

It makes it much easier to help 🙂

Num19
Calcite | Level 5

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

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;
Num19
Calcite | Level 5

Annee are both numeric values in those two tables.

How can I add a table (attachments) ?

 

 

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

 

Kurt_Bremser
Super User

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-dat... 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.

Kurt_Bremser
Super User

@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.

Num19
Calcite | Level 5

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;
Kurt_Bremser
Super User

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;
Num19
Calcite | Level 5

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 ?

Kurt_Bremser
Super User

@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;

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 12 replies
  • 1327 views
  • 0 likes
  • 4 in conversation