HELP ! Compute cols ?

[cody_q]

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_id	Tag_id
100054	0
100058	113;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_id	0	1	2	3	4	5	6	7	8
10054	1	0	0	0	1	0	0	0	0
10056	1	1	1	1	1	1	1	1	1

Any suggestions ?

Thank you.

YL

[Reeza]

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

[cody_q]

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

[cody_q]

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 hear from you soon.

YL

[Reeza]

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 .

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?

[TimCampbell]

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