Desktop productivity for business analysts and programmers

HELP ! Compute cols ?

Reply
Contributor
Posts: 24

HELP ! Compute cols ?

Dear all

File A  : i have a file with 2 columns  ( user_id & tags-ids) .

I want to delimit the tag_id first into ( user_id with single value of Tag_id in each entry)

user_idTag_id
1000540
100058113;41;44;48;91

File B : Another file with 1 columns ( with the list goes on ....)

Tag_id
0
1
2

My desire File C : The cols (0-8 is the tag_ids) .

The value 1 or 0 below it is 1 means have that tag and 0 means no tag.

User_id012345678
10054100010000
10056111111111

Any suggestions ?

Thank you.

YL

Grand Advisor
Posts: 17,396

Re: HELP ! Compute cols ?

Is your example output consistent with the data you've displayed?

Contributor
Posts: 24

Re: HELP ! Compute cols ?

Yes it is . File C is my desired outcome . Just that it has alot of records that's y i didnt show out .

Contributor
Posts: 24

Re: HELP ! Compute cols ?

prt sc.jpg

I have delimited the file.

Any suggestion to change the F1...FN columns to the tag_id of single digits from File B.

Then SAS code it to replace all . with a int value of '0'.

SAS code to display 1 when the no being showned in the col .

Than you for your help Smiley Happy hear from you soon.

YL

Grand Advisor
Posts: 17,396

Re: HELP ! Compute cols ?

I don't understand why ID 10054 has a 1 in 0 and a 1 in 4.

I don't understand your logic at all, but it isn't lunch yet here either Smiley Happy.

It really would help if you expanded the first example to show some numbers that flowed through the tables. Also the previous excel table doesn't look like either tables above. And what do you mean when you say you delimited the file?

Contributor
Posts: 45

Re: HELP ! Compute cols ?

Hi,

Not sure if this is what you are after but let me know this is anywhere close?

     /* Example data */

     data FileA;

          length user_id 8 tag_id $50;

          user_id=100054; tag_id='0'; output;

          user_id=100058; tag_id='113;41;44;48;91'; output;

     run;

    

     /* Count the max number of tags for a user_id */

     proc sql noprint;

          select max(countw(tag_id,';')) into :Tag_Count from FileA;

     quit;

    

     /* split out the Tag_ids from FileA */

     /* Variables F: contain the tag values */

     /* Variables TP: have a value of 1 where a tag was present or 0 where no tag was present */

     Data FileC;

          Set FileA;

          Array Tags{*} F01-F%sysfunc(putn(&Tag_Count,z2.));

          Array Tag_present{*} TP01-TP%sysfunc(putn(&Tag_Count,z2.));

          do i = 1 to &Tag_Count;

               tags{i} = scan(tag_id,i,';');

               if tags{i} ne . then Tag_present{i} = 1;

               else Tag_present{i} = 0;

          end;

          drop tag_id i;

     run;

Tim

Ask a Question
Discussion stats
  • 5 replies
  • 210 views
  • 0 likes
  • 3 in conversation