Exploring, modeling, predicting and reporting with SAS Visual Analytics and SAS Visual Statistics

Union on SAS VA

Reply
Occasional Contributor
Posts: 18

Union on SAS VA

Hi, 

 

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

 

TemporaryTableWithColumnsCombined.png

 

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

 

SAS Employee
Posts: 35

Re: Union on SAS VA

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#n0bk4cm9klgbh3n1jxpb8...

 

Best regards,

Lorrie 

 

Occasional Contributor
Posts: 18

Re: Union on SAS VA

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
SAS Employee
Posts: 35

Re: Union on SAS VA

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:

 

Dataset.png

 

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

 

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:

 

Ds2 Example.png

 

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

 

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:

 

 ProcSQL Example.png

 

 

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#n10uk49nxnzpmcn0zp4na...

 

Best regards,

Lorrie

Ask a Question
Discussion stats
  • 3 replies
  • 378 views
  • 1 like
  • 2 in conversation