topic Re: adding row to data set, mostly duplicate but some different in SAS Programming

adding row to data set, mostly duplicate but some different

geneshackman — Mon, 04 Sep 2023 17:45:37 GMT

Hi all. I hope folks are healthy!

I have a data set and I want to add some rows at the end. The new rows aren't in another data set, so I have to add them manually. I'm attaching an excel file which is a very simplified version of the data set I'm working with. The actual data set has lots more columns (30) and 6k rows, so I'm not sure it would be efficient to use insert into. The added rows would be mostly duplicates of existing rows but with just a couple of changes to values. In the example I'm using, I just want to change the values for sub-group and measure in the new rows. In this particular example data set, row 9 is a duplicate of row 2 except the values for sub-group and measure, and row 10 is a duplicate of row 4 except the values for sub-group and measure.

I'm thinking to duplicate specific rows and then change the values where I want to? Is there an easy way to do this? Also, assume the original data set is in SAS. I'm just using excel because it's quick and also I can add in what the new rows would look like.

Thanks

Re: adding row to data set, mostly duplicate but some different

PaigeMiller — Mon, 04 Sep 2023 17:51:33 GMT

Many of us refuse to download Excel files (and I personally refuse to download any files). The proper way to provide data is to provide it as text in your message, as WORKING data step code (examples and instructions).

In general, based on your description, here is a method that adds 3 rows at the end

data want;
    set sashelp.class end=eof;
    output;
    if eof then do;
         output;
         name='Albert';
         output;
         name='George';
         height=55;
         output;
    end;
run;

Re: adding row to data set, mostly duplicate but some different

Quentin — Mon, 04 Sep 2023 17:54:54 GMT

Hi, this is hard to understand, and many people won't download excel files.

Please edit your question to post a small example of the data you HAVE as a data step with a CARDS statement providing the data.

Then please describe the rules in terms of data you have shown.

Then show the output data you would WANT (also as a data step with CARDS).

Then show the code you have tried.

Re: adding row to data set, mostly duplicate but some different

geneshackman — Mon, 04 Sep 2023 20:30:43 GMT

Interesting that two people say they won't open excel files. On the community support website for tableau, most people say they won't help you unless you include a tableau file they can download, along with data.

Re: adding row to data set, mostly duplicate but some different

geneshackman — Mon, 04 Sep 2023 20:35:05 GMT

Usually if i put data into text, the columns get all messed up and people can't read the data set in a post using text. But here goes.

Original data set
RowNum	Geography	Group	Sub-Group	Measure	Footnote1	Parents	PeerGroup
1	NK	Hair	Long	18	Inches	Same	Different
2	NK	Hair	Short	1	Inches	Different	Same
3	NK	Eyes	Brown	1	1=Brown, 2=Blue,3=Green,4=Combo	Same	Same
4	NK	Eyes	Blue	2	1=Brown, 2=Blue,3=Green,4=Combo	Same	Different
5	NK	Eyes	Green	3	1=Brown, 2=Blue,3=Green,4=Combo	Different	Different
6	NK	Height	Above	6.2	Feet	Different	Same
7	NK	Height	Below	5.7	Feet	Same	Different
8	NK	Height	Average	5.8	Feet	NA	Same

I want to add these rows
9	NK	Hair	None	0	Inches	Different	Same
10	NK	Eyes	BlueGreen	4	1=Brown, 2=Blue,3=Green	Same	Different

Re: adding row to data set, mostly duplicate but some different

geneshackman — Mon, 04 Sep 2023 20:37:43 GMT

Please keep in mind, as I mentioned in my original post, there are about 30 columns, and so it seems not very efficient to list each column and what I want in each column. If the data had fewer columns that might work, but for data with many columns, listing the desired value in each column doesn't seem very useful.

Re: adding row to data set, mostly duplicate but some different

Tom — Mon, 04 Sep 2023 22:05:18 GMT

If you want to share data then share it in a USABLE format.

For example as a simple data step.

So you seem to be saying you have a dataset that is something like this:

data have;
  infile datalines dsd dlm='|' truncover;
  input RowNum Geography $ Group $ SubGroup $ Measure Footnote :$100. Parents :$10. PeerGroup :$10.;
datalines;
1|NK|Hair|Long|18|Inches|Same|Different
2|NK|Hair|Short|1|Inches|Different|Same
3|NK|Eyes|Brown|1|1=Brown, 2=Blue,3=Green,4=Combo|Same|Same
4|NK|Eyes|Blue|2|1=Brown, 2=Blue,3=Green,4=Combo|Same|Different
5|NK|Eyes|Green|3|1=Brown, 2=Blue,3=Green,4=Combo|Different|Different
6|NK|Height|Above|6.2|Feet|Different|Same
7|NK|Height|Below|5.7|Feet|Same|Different
8|NK|Height|Average|5.8|Feet|NA|Same
;

And you want to make a new dataset that is something like this:

data new;
  if 0 then set have;
  infile datalines dsd dlm='|' truncover;
  input RowNum--PeerGroup;
datalines;
9|NK|Hair|None|0|Inches|Different|Same
10|NK|Eyes|BlueGreen|4|1=Brown, 2=Blue,3=Green|Same|Different
;

How are you supposed to derive those two extra rows?

I cannot think of any rule that can go from HAVE to NEW.

Or did you just want to TYPE in the data like I did?

In that case just append the two rows to the original.

proc append base=have data=new ;
run;

Or if you don't want to mess up your original dataset use a simple data step to make another dataset.

data want;
  set have new;
run;

Re: adding row to data set, mostly duplicate but some different

Quentin — Mon, 04 Sep 2023 22:54:28 GMT

@geneshackman wrote:

Interesting that two people say they won't open excel files. On the community support website for tableau, most people say they won't help you unless you include a tableau file they can download, along with data.

The general approach when asking for help from a community is to do as much work as possible to make it easy for people to help you.

So if you describe your data, that doesn't really help me help you. Because if I want to try something, I have to create data myself to play with it. And I might guess wrong.

If you post data as text, it helps a little more, but not much. Because I have to write SAS code to read that text data into a SAS dataset, and after 25 years I'm still pretty bad at that.

If you post an actual SAS data step that creates a small amount of example data that illustrates your problem (does not need all 30 variables), that is helpful. Because I can just run the code you posted, and have data to start with.

So basically you want to post an actual SAS data step, like Tom wrote for you, formatted as a code block.

For tableau folks sharing a tableau file might be the easiest way to share data. But there have been so many excel / VBA viruses over the years, it's too risky to download Excel files from the internet. Definitely for sharing a small amount of data with the SAS community, you want to share code to create that data.

Re: adding row to data set, mostly duplicate but some different

Patrick — Mon, 04 Sep 2023 23:26:13 GMT

@geneshackman wrote:

Interesting that two people say they won't open excel files. On the community support website for tableau, most people say they won't help you unless you include a tableau file they can download, along with data.

It wouldn't be a problem if you add a SAS file/table. It's about safety concerns with MS Office files like Excel and there are quite a few companies that block such downloads.

Re: adding row to data set, mostly duplicate but some different

geneshackman — Mon, 04 Sep 2023 23:44:26 GMT

Thanks for explaining, nicely.

Re: adding row to data set, mostly duplicate but some different

geneshackman — Mon, 04 Sep 2023 23:56:49 GMT

Okay, never mind any more. I figured it out. I don't need any more responses.

Re: adding row to data set, mostly duplicate but some different

ballardw — Tue, 05 Sep 2023 15:17:23 GMT

@geneshackman wrote:

Interesting that two people say they won't open excel files. On the community support website for tableau, most people say they won't help you unless you include a tableau file they can download, along with data.

Different forum, different culture.

Some of us belong to organizations with rules about accessing files. Some, like Excel, are notorious for a number of problems for security.

Another, Excel is a crappy data interchange method. For one thing, it isn't possible to tell if a column should be treated as numeric or character data because columns in Excel can, and entirely too often, do contain mixes of such. So we would have to clean up your example data, as to variable types as a minimum before doing anything. Not to mention getting usable variable names. I've received Excel files with as many as 20 columns named "Total". I just love guessing total of what.

You haven't lived until you get to clean up data provided in Excel that started out as a "fillable PDF" form. 😰