anandbillava Tracker

Re: ods tagsets.excelxp issue

anandbillava — Wed, 02 Jul 2025 11:40:40 GMT

I will wait for tech support to respond to my email and then will send the log.

Re: ods tagsets.excelxp issue

anandbillava — Wed, 02 Jul 2025 11:33:07 GMT

Hi, Can you please remove that folder name from you last post. I accidentally sent it. I try to use that option as well. Created xml, html. No luck so far.

Re: ODS Tagsets.ExcelXP and placing hypelinks to other tabs within same file

anandbillava — Wed, 02 Jul 2025 11:09:09 GMT

I have used this option in the past and it was working for me. Recently I am using a new instance of SAS in a new company and its not working. Is there anything to do with SAS options.? I am using SAS 9.4 M7 PC SAS.

Re: ods tagsets.excelxp issue

anandbillava — Wed, 02 Jul 2025 10:45:33 GMT

ODS excel has limitation. It cannot create report with large number of records and columns. It throws memory error. Thats a known limitation

Re: ods tagsets.excelxp issue

anandbillava — Wed, 02 Jul 2025 10:38:43 GMT

I tried that as well. I am trying to create link for column. Used format and also used an example from forum which worked for others. but not working for me. I am suspecting it may be something to do with my SAS and excel.

ods tagsets.excelxp issue

anandbillava — Wed, 02 Jul 2025 01:42:27 GMT

I have used ods tagsets.excelxp lot of times and never had any issues. But recently I am using it in a new instance of SAS and following things are not working. I have used similar code in the past and never had issues.

The styles using ods escapechar does not work. e.g. title1 "^S={color=CX5F9EA0} Note #2: URGENT " ;

The hyperlink to other sheets using url option (creating format) is not working

Can you please let me know what could be the issue. Is it something to do with excel or SAS version. I am using SAS 9.4 M7 Version (windows).

Re: Adding dropdown list to excel files using ods tagsets

anandbillava — Mon, 26 Jun 2023 21:23:06 GMT

Thats right

Re: Adding dropdown list to excel files using ods tagsets

anandbillava — Mon, 26 Jun 2023 18:46:03 GMT

Thank you for your reply. I am talking about adding data validation list for a perticular cell.

Adding dropdown list to excel files using ods tagsets

anandbillava — Mon, 26 Jun 2023 17:15:34 GMT

Hi,

Is there anyway to add dropdown list to the excel file using ods tagsets or any other ods engine which creates ods excel file.

Thank you,

Ananda

ods tagsets Insert vbscript

anandbillava — Mon, 26 Jun 2023 17:13:44 GMT

I am creating a excel file (xml) with ods tagsets and planning to insert some vbcodes as part of the file. Is there a way to do it ?

Re: Hierachy data (Parent Child)

anandbillava — Tue, 06 Aug 2019 17:12:18 GMT

I will have to create more than 4000 datasets which is am already doing and its taking lot of time to process.

Re: Hierachy data (Parent Child)

anandbillava — Tue, 06 Aug 2019 15:23:55 GMT

Hi ,

I have lot of records in the dataset not just 3. It will take lot of time to process if i write a macro to subset these. Is there a way i can do it in one step.

Thank you,

Re: Hierachy data (Parent Child)

anandbillava — Mon, 05 Aug 2019 20:15:03 GMT

Can you help me in solving problem below. I am providing here the appropriate ancestor for each of the record. I need to find this ancestor for each of the record by user. Can you please help.

data a;

user=1; base=4.1; cur=4.2; output; /* 4.1 to 4.2 - Ancestor here is 4.4 */

user=1; base=4.2; cur=4.4; output; /* 4.2 to 4.4 - Ancestor here is 4.4 */

user=1; base=4.6; cur=5.5; output; /* 4.6 to 5.5 - Ancestor here is 6.1 */

user=1; base=5.5; cur=6.1; output; /* 5.5 to 6.1 - Ancestor here is 6.1 */

user=2; base=4.2; cur=4.6; output; /* 4.2 to 4.6 - Ancestor here is 6.1 */

user=2; base=4.6; cur=5.5; output; /* 4.6 to 5.5 - Ancestor here is 6.1 */

user=2; base=5.5; cur=6.1; output; /* 5.0 to 6.1 - Ancestor here is 6.1 */

user=3; base=4.0; cur=4.2; output; /* 4.0 to 4.2 - Ancestor here is 5.5 */

user=3; base=4.2; cur=4.6; output; /* 4.2 to 4.6 - Ancestor here is 5.5 */

user=3; base=4.6; cur=5.5; output; /* 4.6 to 6.1 - Ancestor here is 5.5 */

run;

I am able to achieve this if there is only one user in the dataset using code below. But its not working if there are more than one user.

data _tmp1(rename=(base=base1 cur=cur1));

set a;

run;

**Repeat the training module for each of the delta version between base and current module;

data _tmp2;

retain base cur;

set _tmp1; by u;

cur = cur1;

base = base1;

output;

drop base1 cur1 i;

run;

**Get all the base version for the current modue which has training then need to check the

corresponding base version full access;

data _lineage;

set _tmp2; by u;

array hist(6);

count=1;

hist(count)=base;

do i=1 to last while (count<dim(hist)-1);

set _tmp2(rename=(base=base1 cur=cur1)) nobs=last point=i;

if cur=base1 then do;

count+1;

hist(count)=base1;

base=base1;

cur=cur1;

i=1;

end;

count+1;

hist(count)=cur;

ancest = cur;

run;

Re: Hierachy data (Parent Child)

anandbillava — Thu, 30 Aug 2018 19:35:06 GMT

Thank you.. I am able to solve this issue with another approach. Thanks for all your help

Re: Get ancestors for each children

anandbillava — Wed, 29 Aug 2018 11:32:10 GMT

Here it is

data test;
input child parent;
datalines;
23 11
99 88
77 66
72 68
88 77
66 1
68 55
69 55
70 55
55 56
03 99

;

data lineage(keep=hist1-hist10 flag);

set test;

array hist(10);
count=1;

hist(count)=child;

do i=1 to last while (count<dim(hist)-1);

set test(rename=(child=child1 parent=parent1)) nobs=last point=i;

if parent=child1 then do;
count+1;

hist(count)=child1;
child=child1;
parent=parent1;

i=1;
end;
end;

count+1;
hist(count)=parent;
flag = parent;
run;

Re: Get ancestors for each children

anandbillava — Wed, 29 Aug 2018 00:19:54 GMT

Thank you. I am able to do the same thing through another means using point and last in the data step

Get ancestors for each children

anandbillava — Tue, 28 Aug 2018 19:07:48 GMT

data HIERARCHY;

input Parent Child;

datalines;

. 1

1 2

1 3

1 8

1 9

2 4

3 5

5 6

6 7

8 10

10 11

10 13;

run;

for the above dataset i want to find the ancestor lineage for each of the records. Can you please help.

Output would look as below.

datalines;

parent child ancestor_lineage

1 2 1

1 3 1

1 8 1

1 9 1

2 4 2,1

3 5 3.1

5 6 5,3,1

6 7 6,5,3,1

8 10 8

10 11 10, 8

10 13; 10,8

Re: Hierachy data (Parent Child)

anandbillava — Tue, 28 Aug 2018 15:30:52 GMT

Thank you very much. Its kind of working. But I need to output this based on value a and c. In this case the variable value for a is 1 and c is 5.5.

data a;
a=1; b=5.1; c=5.5; output;
a=1; b=5.2; c=5.5; output;
a=1; b=5.0; c=5.2; output;
a=1; b=4.7; c=5.0; output;
a=1; b=4.6; c=5.2; output;
a=1; b=.; c=4.6; output;

a=2; b=5.4; c=6.1; output;
a=2; b=5.3; c=6.1; output;
a=2; b=5.0; c=5.3; output;
a=2; b=4.7; c=5.0; output;
a=2; b=4.5; c=4.7; output;
run;

For the second set of record where a=2 and lets say value c is 6.1 the children output must be as below

5.4

4.5

Only these two do not have children.

I am sorry for not mentioning this case earlier.

run;

Re: Hierachy data (Parent Child)

anandbillava — Tue, 28 Aug 2018 12:28:24 GMT

I this case the output will be

new variable

5.1

4.7

4.6

All this do not have any child nodes or children for the ancestor or parent 5.5.

Basically when user asks variable c with value 5.5 then output should be look like above.

Steps are

1. The program reads c variable where value is 5.5 and then looks for the children which is variable b and the values are 5.1 and 5.2

2. For each of b value which is 5.1 and 5.2 the program looks for children and their children are for 5.1 no children so it will appear in the report and 5.2 has children 5.0... and it goes on for all the childrens...

Hierachy data (Parent Child)

anandbillava — Mon, 27 Aug 2018 17:55:17 GMT

Hi There,

I have a data like this

data a;
a=1; b=5.1; c=5.5; output;
a=1; b=5.2; c=5.5; output;
a=1; b=5.0; c=5.2; output;

a=1; b=4.7; c=5.0; output;
a=1; b=4.6; c=5.2; output;

a=1; b=.; c=4.6; output;

run;

I am trying to create a output in which for a given parent nodie (variable c) it should get all the childs/nodes (variable b) which should not have any child/nodes. for e.g in the above example for parent node with value 5.5 it should output 5.1, 4.7 and 4.6 which do not have any child.

Here is how the output is picked.

5.5 has 5.1 and 5.2 child, then 5.2 has child 5.0, 5.0 has child 4.7 and 5.2 has child 4.6. 4.6, 4.7 and 5.1 do not have any childs.

Please let me know if you need more expalnation.

Thanks in advance for the help.

Anand