Columns are missing while merging

[NareshAbburi]

hi,

I've two tables which needs to be merged. While doing so, I'm being left with only few columns in the resultant table.

Why so....?

data logfile_final_UI7;

merge logfile_new new_parse;

end;

Please help me

[ballardw]

How about some details such as:

Variables in logfile_new

Variable in new_parse

And the missing columns.

A few rows of data from each file and the result would also be helpful.

[NareshAbburi]

Hi

Please find the attached.

[NareshAbburi]

[ballardw]

You haven't said which column is missing or provided an example of the actual output.

And the second set of code references two different data sets.

Do the two datasets have the same number of rows? Without a by  statement to line things up then you may be getting missing results because of the numbers of rows not matching.

And instead of that many If then else statements:

array a Action1-action40;

action_count=0;

do j = dim(a) to 1;

     if a ne ' ' then action_count=j;

     if action_count > 0 then leave;

end;

[NareshAbburi]

hi,

this is the output table. your answer helped to make an array instead of big query.

but the missing column is FILEID.

[Reeza]

Your code looks incorrect. I would expect a merge to have a BY statement.

data logfile_final_UI7;

merge logfile_new new_parse;

BY fileid;

****other sas code here***;

run;

[ballardw]

Run proc contents on the two input files and the result files and post that information. Screen shots of data sets aren't that helpful as they don't show all of the columns.

[RamKumar]

What needs to be done if I need to create a new variable based on looping?

  if a ne ' ' then action_count=j;

[RamKumar]

any suggestions?

[LinusH]

I haven't dug into you problem, but your code clearly shouts: Transpose My Data Set!

[Patrick]

pointed already out that you need a "by" statement for your merge and I totally agree with that having a long instead of a wide data structure is very often beneficial.

Under the assumption that your Action1 to Action40 variables are populated starting with Action1 and that once there is a missing all later Action variables are also missing, then one could simply count the number of non-missing variables. That's what's done in below code.

data have(drop=_i);

  length rowid 8;

  array action {40} 8.;

  do rowid=1 to 20;

    do _i=1 to dim(action);

      action[_i]=1;

      if ceil(ranuni(1)*40)=20 then leave;

    end;

    output;

    call missing(of action

);

  end;

  stop;

run;

data want;

  length rowid action_count 8;

  set have;

  array action {*} action1-action40;

  action_count=n(of action

);

run;