topic Re: Union on SAS VA in SAS Visual Analytics

Union on SAS VA

vasireddyajay — Tue, 08 Dec 2015 14:04:46 GMT

Hi,

I am having some challenge to do the following in SAS VA i.e, to generate MycarTable from CarTable.

Please guide me how to create a table(MyCarTable) with original columns and a new column that is a union of first two columns.

Thanks.

Best regards,

Ajay

Re: Union on SAS VA

Lorrie_SAS — Tue, 08 Dec 2015 15:59:52 GMT

Hi Ajay,

From the Visual Data Builder, try creating a data query and adding your table. After you add the existing columns by clicking on the names, create a new column by clicking the plus sign (+) on the Column Editor tab, and then create a union expression for the new column. Here's some documentation that may be helpful:

http://support.sas.com/documentation/cdl/en/vaug/68648/HTML/default/viewer.htm#n0bk4cm9klgbh3n1jxpb8lfxu12p.htm

Best regards,

Lorrie

Re: Union on SAS VA

vasireddyajay — Tue, 08 Dec 2015 16:08:54 GMT

Hi Lorrie,

thank you for your comment. I tried to use the (+) but when I try to use select statement, in the expression, I am getting exceptions. Could you please let me know the expression you used to create a new column that joins the columns from the same table.

Thanks.

Kind regards,
Ajay

Re: Union on SAS VA

Lorrie_SAS — Fri, 11 Dec 2015 18:49:54 GMT

Hi Ajay,

I did some more research to see how we can get this to work, and it looks like the only way would be for you to modify the code directly. Here are some options and what the results would looks like:

For example:

data cars;
  keep Petrol Diesel;
  merge sashelp.cars (where=(Origin='Asia') obs=5
        rename=(Model=Diesel))
        sashelp.cars (where=(Origin='Europe')obs=8
        rename=(Model=Petrol));
run;

Example original dataset:

------------------------------------------

Option 1:

proc ds2;
TITLE 'Using DS2';
data;
  method run();
  set {select * from
  cars
  full join
  (select Petrol as AllCars
    from cars
    union all
    select Diesel from cars
    ) as a
  on cars.Petrol=a.AllCars};
  if not missing(cat(Petrol,Diesel,Allcars));
  end;
enddata;
run;
quit;

Option 1 results:

----------------------------------------------------------

Option 2:

proc sql;
TITLE 'Using PROC SQL';
select * from
  cars
  full join
  (select Petrol as AllCars
    from cars
    union all
    select Diesel from cars
    ) as a
  on cars.Petrol=a.AllCars
  where cats(Petrol,Diesel,Allcars) is not null
;
quit;

Option 2 results:

In the case that you wanted to have the data union appear in a seperate table (instead of combined with the original columns), the answer is a lot more straightforward. You would go to the data builder and click LASR > Append Tables to get started. Here is a link to more detailed information about appending tables:

http://support.sas.com/documentation/cdl/en/vaug/68648/HTML/default/viewer.htm#n10uk49nxnzpmcn0zp4na9bn261v.htm

Best regards,

Lorrie