SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Columns are missing while merging

Reply
Contributor
Posts: 40

Columns are missing while merging

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

Super User
Posts: 10,500

Re: Columns are missing while merging

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.

Contributor
Posts: 40

Re: Columns are missing while merging

Hi

Please find the attached.

file1.PNGfile2.PNG

Contributor
Posts: 40

Re: Columns are missing while merging

query.PNG

Super User
Posts: 10,500

Re: Columns are missing while merging

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;

Contributor
Posts: 40

Re: Columns are missing while merging

hi,

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

but the missing column is FILEID.

output.PNG

Super User
Posts: 17,836

Re: Columns are missing while merging

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;

Super User
Posts: 10,500

Re: Columns are missing while merging

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.

Regular Contributor
Posts: 168

Re: Columns are missing while merging

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

  if a ne ' ' then action_count=j;

Regular Contributor
Posts: 168

Re: Columns are missing while merging

any suggestions?

Super User
Posts: 5,257

Re: Columns are missing while merging

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

Data never sleeps
Respected Advisor
Posts: 3,893

Re: Columns are missing while merging

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;

    Ask a Question
    Discussion stats
    • 11 replies
    • 565 views
    • 3 likes
    • 6 in conversation