BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
chuck33
Fluorite | Level 6

Hi,

I am trying to create a data record which contains non aligned fields. My have written the code as in attached editor file.

However I am not able to correctly render the ‘Details’ variable as shown in image below (it's only correct as shown in first observation)

Can somebody please suggest modifications in the code to read it correctly? (Please see attached lign code)

Your help is much appreciated.

Thanks.

1 ACCEPTED SOLUTION

Accepted Solutions
slchen
Lapis Lazuli | Level 10

I did this as Patrick suggested, maybe it will be helpful.

data joseph_balances;

input name$ 1-6 @13 amount  dollar7. /string $50.//;

datail=prxchange('s/(.*)\s(?=\d{1,2}\/\d{1,2}\/\d+).*/$1/i',-1,string);

date=put(input(prxchange('s/.*\s(\d{1,2}\/\d{1,2}\/\d+).*/$1/',-1,string),anydtdte.),mmddyy8.);

time=put(input(prxchange('s/.*\s(\d+:\d+ (AM|PM))/$1/',-1,string),time8.),time8.);

drop string;

cards;

joseph paid $18.96

DTE March 15 5/4/15 12:19 PM

for: All

joseph paid $25.00

Internet March 15 4/10/15 8:54 PM

for: All

joseph paid $26.18

DTE Feb 2/24/15 7:28 AM

for: All

joseph paid $610.00

Rent March 15 2/21/15 10:43 AM

for: All

joseph paid $25.00

Internet Jan 15 2/11/15 12:11 PM

for: All

joseph paid $610.00

Rent Feb 15 2/3/15 2:42 PM

for: All

joseph paid $26.54

DTE Jan 15 1/21/15 11:55 AM

for: All

joseph paid $25.00

Internet December 14 1/15/15 3:27 PM

for: All

joseph paid $72.00

Book 1/8/15 3:27 PM

for: All

joseph paid $23.47

DTE Dec 13 1/4/15 3:47 PM

for: All

joseph paid $610.00

Rent Jan 2015 1/1/15 8:28 PM

for: All

joseph paid $25.00

Internet November 12/2/14 8:28 PM

for: All

joseph paid $610.00

Rent Dec 12/2/14 8:28 PM

for: All

joseph paid $23.77

DTE November 11/19/14 8:03 PM

for: All

joseph paid $610.00

Rent November 11/2/14 1:08 PM

for: All

edith gave $102.00

Debt settlement 10/28/14 8:51 PM

to: joseph

joseph paid $55.00

Internet Sep Oct 10/27/14 4:35 PM

for: All

joseph paid $24.59

DTE oct 10/23/14 10:05 PM

for: All

joseph paid $102.00

Licence 10/1/14 3:39 PM

for: edith

joseph paid $610.00

Rent oct 10/1/14 12:08 PM

for: All

joseph paid $23.63

DTE sept 9/19/14 3:45 PM

for: All

;

proc print;

run;

View solution in original post

4 REPLIES 4
Patrick
Opal | Level 21

Internet March 15 4/10/15 8:54 PM

I believe one way which could work is to read the whole 3rd line as a string, then find the position of the 3rd last word in this string.


Once you've got this position read the first bit of the string into your character variable and the second bit (starting from the 3rd last word) as a datetime value.



slchen
Lapis Lazuli | Level 10

I did this as Patrick suggested, maybe it will be helpful.

data joseph_balances;

input name$ 1-6 @13 amount  dollar7. /string $50.//;

datail=prxchange('s/(.*)\s(?=\d{1,2}\/\d{1,2}\/\d+).*/$1/i',-1,string);

date=put(input(prxchange('s/.*\s(\d{1,2}\/\d{1,2}\/\d+).*/$1/',-1,string),anydtdte.),mmddyy8.);

time=put(input(prxchange('s/.*\s(\d+:\d+ (AM|PM))/$1/',-1,string),time8.),time8.);

drop string;

cards;

joseph paid $18.96

DTE March 15 5/4/15 12:19 PM

for: All

joseph paid $25.00

Internet March 15 4/10/15 8:54 PM

for: All

joseph paid $26.18

DTE Feb 2/24/15 7:28 AM

for: All

joseph paid $610.00

Rent March 15 2/21/15 10:43 AM

for: All

joseph paid $25.00

Internet Jan 15 2/11/15 12:11 PM

for: All

joseph paid $610.00

Rent Feb 15 2/3/15 2:42 PM

for: All

joseph paid $26.54

DTE Jan 15 1/21/15 11:55 AM

for: All

joseph paid $25.00

Internet December 14 1/15/15 3:27 PM

for: All

joseph paid $72.00

Book 1/8/15 3:27 PM

for: All

joseph paid $23.47

DTE Dec 13 1/4/15 3:47 PM

for: All

joseph paid $610.00

Rent Jan 2015 1/1/15 8:28 PM

for: All

joseph paid $25.00

Internet November 12/2/14 8:28 PM

for: All

joseph paid $610.00

Rent Dec 12/2/14 8:28 PM

for: All

joseph paid $23.77

DTE November 11/19/14 8:03 PM

for: All

joseph paid $610.00

Rent November 11/2/14 1:08 PM

for: All

edith gave $102.00

Debt settlement 10/28/14 8:51 PM

to: joseph

joseph paid $55.00

Internet Sep Oct 10/27/14 4:35 PM

for: All

joseph paid $24.59

DTE oct 10/23/14 10:05 PM

for: All

joseph paid $102.00

Licence 10/1/14 3:39 PM

for: edith

joseph paid $610.00

Rent oct 10/1/14 12:08 PM

for: All

joseph paid $23.63

DTE sept 9/19/14 3:45 PM

for: All

;

proc print;

run;

Patrick
Opal | Level 21

Just as a variation to the code already provided using different RegEx functions.

data joseph_balances;

  if _N_=1 then

      _re=prxparse('/(.+)\b(\d+\/\d+\/\d+ \d+:\d+ (PM|AM))/i');

  retain _re;

  infile datalines truncover;

  input

        name$ 1-6 @13 amount  dollar7.

      / string $50.//;

  if prxmatch(_re, string) then

    do;

       detail=prxposn(_re, 1, string);

       dttm=input(prxposn(_re, 2, string),mdyampm25.2);

    end;

  format dttm datetime20.;

  drop string;

  cards;

......

chuck33
Fluorite | Level 6

Thanks,

This was too difficult for me.

Your time and efforts are much appreciated.

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 866 views
  • 5 likes
  • 3 in conversation