BookmarkSubscribeRSS Feed
NareshAbburi
Calcite | Level 5

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

11 REPLIES 11
ballardw
Super User

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
Calcite | Level 5

Hi

Please find the attached.

file1.PNGfile2.PNG

ballardw
Super User

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
Calcite | Level 5

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

Reeza
Super User

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
Super User

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
Fluorite | Level 6

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

  if a ne ' ' then action_count=j;

RamKumar
Fluorite | Level 6

any suggestions?

LinusH
Tourmaline | Level 20

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

Data never sleeps
Patrick
Opal | Level 21

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;

    sas-innovate-2024.png

    Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

    Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

     

    Register now!

    How to connect to databases in SAS Viya

    Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

    Find more tutorials on the SAS Users YouTube channel.

    Discussion stats
    • 11 replies
    • 1506 views
    • 3 likes
    • 6 in conversation