topic Importing unstructured data stored in varying number of lines in SAS Programming

Importing unstructured data stored in varying number of lines

ghosh — Sun, 22 Jan 2023 22:36:59 GMT

As a personal project I would like to import Instagram data which I have dummied up in the file igdata_dummy.txt

The data file structure is as follows, each on separate line:
1. Header record is as "Username's profile picture"
2. Username's ID
3. Period "." if he Username is not followed back
4. Username's name (optional)

filename igdat  '~/dat/igdata_dummy.txt';
data want;
  infile igdat truncover end=eof;
  length header $200 ID_ig $50 Follower $1 Username $100 ;
      input @@ ;
      if scan(_infile_,-1)= 'picture' and not eof then do;
        input header &;
        input ID_ig;
       if _infile_= '.' then 
       input Follower ;       
        else input Username &;        
     end; 
     output;   
   run;
proc print n;
run;

Content of file igdata_dummy.txt

Johnsmith's profile picture
Johnsmith
·
John Smith
BW_photo's profile picture
BW_photo
Ansel Adams
IG.user's profile picture
IG.user

Desired output

I would appreciate your suggestions in importing this data set.

Thanks

Re: Importing unstructured data stored in varying number of lines

Ksharp — Mon, 23 Jan 2023 04:41:41 GMT

data have;
infile cards truncover ;
input have $100.;
cards;
Johnsmith's profile picture
Johnsmith
·
John Smith
BW_photo's profile picture
BW_photo
Ansel Adams
IG.user's profile picture
IG.user
;
run;
data temp;
 set have;
 length temp name $ 100;
 retain temp;
 if findw(have,'profile') and findw(have,'picture') then do;
  id+1;n=1;temp=scan(have,1,"'");name='header';
 end;
    else if  have=:temp then do;n=2;name='ID_ig';end;
      else if strip(have)='·' or missing(have) then do;n=3;name='Follower';end;
	    else do;n=4;name='Username';end;
drop temp;
run;
proc transpose data=temp out=want(drop=_:);
by id;
id name;
var have;
run;

Re: Importing unstructured data stored in varying number of lines

ghosh — Mon, 23 Jan 2023 21:41:56 GMT

Hello @Ksharp

Thanks for taking the time to give me a solution. However, if the Username's ID and the Username's name have identical values it throws an error. I have updated the dummy data to re-create the error shown below

data have;
infile cards truncover ;
input have $100.;
cards;
Johnsmith's profile picture
Johnsmith
·
Johnsmith
BW_photo's profile picture
BW_photo
Ansel Adams
IG.user's profile picture
IG.user
IG.user
;
run;
data temp;
 set have;
 length temp name $ 100;
 retain temp;
 if findw(have,'profile') and findw(have,'picture') then do;
  id+1;n=1;temp=scan(have,1,"'");name='header';
 end;
    else if  have=:temp then do;n=2;name='ID_ig';end;
      else if strip(have)='·' or missing(have) then do;n=3;name='Follower';end;
	    else do;n=4;name='Username';end;
drop temp;
run;
proc transpose data=temp out=want(drop=_:);
by id;
id name;
var have;
run;

Re: Importing unstructured data stored in varying number of lines

Ksharp — Tue, 24 Jan 2023 04:12:05 GMT

OK. Try this one :

data have;
infile cards truncover ;
input have $100.;
cards;
Johnsmith's profile picture
Johnsmith
·
Johnsmith
BW_photo's profile picture
BW_photo
Ansel Adams
IG.user's profile picture
IG.user
IG.user
;
run;
data temp;
 set have;
 length temp name $ 100;
 retain temp found;
 if findw(have,'profile') and findw(have,'picture') then do;
  id+1;n=1;temp=scan(have,1,"'");name='header';found=0;
 end;
    else if  have=:temp and not found then do;n=2;name='ID_ig';found=1;end;
      else if strip(have)='·' or missing(have) then do;n=3;name='Follower';end;
	    else do;n=4;name='Username';end;
drop temp found;
run;
proc transpose data=temp out=want(drop=_:);
by id;
id name;
var have;
run;

Re: Importing unstructured data stored in varying number of lines

ghosh — Wed, 25 Jan 2023 03:46:12 GMT

thanks!