BookmarkSubscribeRSS Feed
songforu-8sdsa
Calcite | Level 5

Hello everyone

How do I input the below dataline into sas with variable : gender, birthday, name, status(last letter of each line) for fisrt dataset

datalines;

M10/21/46CADYA
F11/11/50CLINEB
M11/11/52SMITHA
F10/10/80OPPENHEIMERB
M04/04/60JOSEA

 

second dataset with variables gender birthday name height

datalines:

M10/21/46CODY155
F11/11/50CLEIN166
F11/11/52SMITH185
F10/10/80OPPENHAIMER199
M02/07/60JOSA173

 

And I also want to keep those rows that are same(matches) in two datalines with all variables(including status and height)

4 REPLIES 4
nehalsanghvi
Pyrite | Level 9

Hi, see code below. None of your data matches up the way you listed it because either names are different or gender is different, I don't know if those are typos in the names or if that's how your data is. But I changed the first name in the second dataset so you can see what the dataset 'want' looks like if all three values match.

data have1;
input Gender $ 1 DOBin $ 2-9 NameStatus $ 10-25;
datalines;
M10/21/46CADYA
F11/11/50CLINEB
M11/11/52SMITHA
F10/10/80OPPENHEIMERB
M04/04/60JOSEA
;
run;

data have1;
set have1;
format DOB mmddyy8.;
DOB=input(DOBin,mmddyy8.);
Name=substr(NameStatus,1,length(NameStatus)-1);
Status=substr(NameStatus,length(NameStatus));
drop DOBin NameStatus;
run;

proc sort data=have1;
by gender DOB Name;
run;

data have2;
input Gender $ 1 DOBin $ 2-9 NameHeight $ 10-25;
datalines;
M10/21/46CADY155
F11/11/50CLEIN166
F11/11/52SMITH185
F10/10/80OPPENHAIMER199
M02/07/60JOSA173
;
run;

data have2;
set have2;
format DOB mmddyy8.;
DOB=input(DOBin,mmddyy8.);
Name=compress(NameHeight,'','d');
Height=input(compress(NameHeight,'','a'),3.);
drop DOBin NameHeight;
run;

proc sort data=have2;
by gender DOB Name;
run;

data want;
merge have1(in=a) have2(in=b);
by gender DOB Name;
if a and b;
run;
Yavuz
Quartz | Level 8

i prepared 2 different datasets have and have2. i tried they are correct. if you want you can change data formats.


/*                 part one             */

data have;
INPUT comment $ 30.;
DATALINES;
M10/21/46CADYA
F11/11/50CLINEB
M11/11/52SMITHA
F10/10/80OPPENHEIMERB
M04/04/60JOSEA
;
RUN;

PROC SQL;
CREATE TABLE WORK.QUERY_FOR_HAVE AS
SELECT /* gender */
(SUBSTR(t1.comment, 1, 1)) AS gender,
/* birthday */
(SUBSTR(t1.comment,2,8)) AS birthday,
/* status */
(substr(t1.comment,(LENGTH(t1.comment)),1)) AS status,
/* name */
(substr(t1.comment,10, LENGTH(t1.comment)-10)) AS name
FROM WORK.HAVE t1;
QUIT;

 

/*                        part two                         */


data have2;
INPUT comment2 $ 30.;
DATALINES;

M10/21/46CODY155
F11/11/50CLEIN166
F11/11/52SMITH185
;
run;

 

PROC SQL;
CREATE TABLE WORK.QUERY_FOR_HAVE_2 AS
SELECT /* gender */
(SUBSTR(t1.comment2, 1, 1)) AS gender,
/* birthday */
(SUBSTR(t1.comment2,2,8)) AS birthday,
/* status */
(substr(t1.comment2,(LENGTH(t1.comment2))-2,3)) AS status,
/* name */
(substr(t1.comment2,10, LENGTH(t1.comment2)-12)) AS name
FROM WORK.HAVE2 t1;
QUIT;

 

Haikuo
Onyx | Level 15

This kinda of question always brought me back to the good old days when programming does matter :), You will need to read SAS docs on input, infile, informats etc.

 

 

filename FT15F001 temp lrecl=512; 

data test;
infile FT15F001  length=len ;
input @;
len=len-10;
input gender $1.
       birthday mmddyy8. 
        name $varying100. len status $1.;

format birthday mmddyy8.;
parmcards4;;;;
M10/21/46CADYA
F11/11/50CLINEB
M11/11/52SMITHA
F10/10/80OPPENHEIMERB
M04/04/60JOSEA
;;;;
run;

filename FT15F001 clear;


filename FT15F001 temp lrecl=512; 

data test2;
infile FT15F001  length=len ;
input @;
len=len-12;
input gender $1.
       birthday mmddyy8. 
        name $varying100. len height;

format birthday mmddyy8.;
parmcards4;;;;
M10/21/46CODY155
F11/11/50CLEIN166
F11/11/52SMITH185
F10/10/80OPPENHAIMER199
M02/07/60JOSA173
;;;;
run;

filename FT15F001 clear;
PGStats
Opal | Level 21

I added one dataline to show a matching case:

 

data a;
length gender $1 name $20 birthday 8 status $1 line $100;
format birthday yymmdd10.;
if not prxId then prxId + prxParse("@([MF])(\d{1,2}/\d{1,2}/\d{1,2})(\D+)(\w)@");
input line;
if prxMatch(prxId, line) then do;
    gender = prxPosn(prxId, 1, line);
    birthday = input(prxPosn(prxId, 2, line), mmddyy8.);
    name = prxPosn(prxId, 3, line);
    status = prxPosn(prxId, 4, line);
    output;
    end;
drop line prxId;
datalines;
M10/21/46CADYA
F11/11/50CLINEB
M11/11/52SMITHA
F10/10/80OPPENHEIMERB
M04/04/60JOSEA
F11/11/52SMITHA
;

data b;
length gender $1 name $20 birthday 8 weight 8 line $100;
format birthday yymmdd10.;
if not prxId then prxId + prxParse("@([MF])(\d{1,2}/\d{1,2}/\d{1,2})(\D+)(\d+)@");
input line;
if prxMatch(prxId, line) then do;
    gender = prxPosn(prxId, 1, line);
    birthday = input(prxPosn(prxId, 2, line), mmddyy8.);
    name = prxPosn(prxId, 3, line);
    weight = input(prxPosn(prxId, 4, line), best.);
    output;
    end;
drop line prxId;
datalines;
M10/21/46CODY155
F11/11/50CLEIN166
F11/11/52SMITH185
F10/10/80OPPENHAIMER199
M02/07/60JOSA173
;

proc sql;
create table ab as
select a.*, b.weight
from a natural join b;
select * from ab;
quit;

PG

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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
  • 4 replies
  • 773 views
  • 1 like
  • 5 in conversation