topic Re: Adding columns based on condition in SAS Enterprise Guide

Adding columns based on condition

matus2711 — Mon, 08 Feb 2021 15:36:42 GMT

Hi everyone, i am beginner despite of SAS and i would be glad if you help me with this problem.
Imagine you have columns for example:
V1 V2 V3 V4 V5 V6 V7
Now i want to add code which complete this row of columns to V30. But you don't know which column is the last one. So you have to find him and based on this info complete row of columns to V30.
Assume that columns which are already there could contain some data but it's not neccesary.
I hope my description is sufficient. If not i can add some more details. Thank you very much for willingnes and your time.

Re: Adding columns based on condition

PaigeMiller — Mon, 08 Feb 2021 15:30:06 GMT

So you have to find them ...

Find them how? Yes, please explain in more detail.

Re: Adding columns based on condition

matus2711 — Mon, 08 Feb 2021 15:35:32 GMT

Find that last column, in whole dataset it can be any column, but more often the last one is V13, V14 or V15 for example. So for example you detect that last column in this part of dataset is V13 so you then just add others to number 30. My problem is that i don't know which functions are best for it.

Re: Adding columns based on condition

PaigeMiller — Mon, 08 Feb 2021 15:39:16 GMT

@matus2711 wrote:

Find that last column, in whole dataset it can be any column, but more often the last one is V13, V14 or V15 for example. So for example you detect that last column in this part of dataset is V13 so you then just add others to number 30. My problem is that i don't know which functions are best for it.

I'm afraid I can't grasp enough meaning here to write a program. Show us a small example, explain what you have and what you want to have.

Re: Adding columns based on condition

PhilC — Mon, 08 Feb 2021 17:33:47 GMT

I would need more information, as well.

but to start to answer your questions:

1) This is not an Enterprise Guide question, it is a SAS programming question.

2) One can add column using this syntax.

data want;
  set have;
  length v8-v30 8;
run;

that's going to add 23 numeric columns to your data.

3) This code will access the metadata of the dataset "HAVE" and create a table, one row per column.

proc sql;
  select *
    from dictionary.Columns
    where libname="WORK"
      AND memname="HAVE"
  ;
quit;

using this type of query one can "count" how many columns are in a dataset.

Re: Adding columns based on condition

matus2711 — Mon, 08 Feb 2021 15:52:03 GMT

Imagine you have columns, e.g.
V1 V2 V3 V4 V5
1. detect or find number of last column, i don't want hardcode them
2. complete row of columns (V1 V2 V3 V4 V5) to V30
Which type of additional info you need for this?

Re: Adding columns based on condition

PaigeMiller — Mon, 08 Feb 2021 16:01:17 GMT

@matus2711 wrote:
Imagine you have columns, e.g.
V1 V2 V3 V4 V5
1. detect or find number of last column, i don't want hardcode them
2. complete row of columns (V1 V2 V3 V4 V5) to V30
Which type of additional info you need for this?

Your examples keep talking about V30, why? What does that have to do with the fact that you have columns V1 V2 V3 V4 V5?

Re: Adding columns based on condition

PhilC — Mon, 08 Feb 2021 16:04:01 GMT

OK, question then, if one might imagine the variables B1 through B5. Would one want, then, variables B6 through B30? That is are you wanting to keep the same prefix? in your case "V" and in this case "B".

Re: Adding columns based on condition

matus2711 — Mon, 08 Feb 2021 17:03:15 GMT

Exactly i want to keep same prefix. thank you for patience

Re: Adding columns based on condition

Dani_Gor — Mon, 08 Feb 2021 17:06:21 GMT

Hi,

I'm trying to understand,

you have something like this table:

do you want to add the missing columns (in this example from V13 to V30) to the table?

Re: Adding columns based on condition

matus2711 — Mon, 08 Feb 2021 17:06:36 GMT

Because i want to expand dataset to column V30 or B30 or P30, that doesn't matter. Sorry for inaccurate description.

Re: Adding columns based on condition

matus2711 — Mon, 08 Feb 2021 17:08:56 GMT

Yes exactly but you have to keep assumption that in dataset there are not all the columns same and because of that i can't hardcode them by e.g. alter table command.

Re: Adding columns based on condition

Tom — Mon, 08 Feb 2021 17:37:43 GMT

Do not think of it as ADDING variables. Think of it as assuring that ALL of the variables are defined. So if you want make sure that the variables V1 to V30 are all defined then just add a statement that defines them. Like a LENGTH statement.

So if V1 to V30 are supposed to be numeric you would do something like this:

data want ;
  length v1-v30 8;
  set have;
run;

If they are supposed to be character then use $ before the length value in the LENGTH statement.

Re: Adding columns based on condition

matus2711 — Mon, 08 Feb 2021 18:03:00 GMT

Thank you very much. Amazing how simple the solution is 👍

Re: Adding columns based on condition

PhilC — Mon, 08 Feb 2021 20:07:47 GMT

Now I see. If you care to understand more about this subject in SAS in the
future, a good term to remember is SAS "Program data vector" or PDV.
Which is what I would have spoken to if I saw what you were dealing with in
time. There was a similar question asked about ordering of variables in
the last two weeks.